Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

how to script for finding maximum, minimum date value in a row for 100+ columns

Oct 13, 2020 10:41 AM
(307 views)

i have a table with 500+ rows and 100+ columns. Populated with numeric values (dates). I need to find the maximum and minimum value along each row for earliest and latest date. i guess the script would be similar to 'Maximum (column1........column100)' for each row. But i am not sure how to script the column ranges along each row.

10 REPLIES 10

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: how to script for finding maximum, minimum date value in a row for 100+ columns

Hi,

Have you considered transposing your data (Rows become Column and reciprocaly)? You would then be able to use the Col Minimum and Col Maximum formulas.

Best,

TS

Have you considered transposing your data (Rows become Column and reciprocaly)? You would then be able to use the Col Minimum and Col Maximum formulas.

Best,

TS

Thierry R. Sornasse

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: how to script for finding maximum, minimum date value in a row for 100+ columns

Hi @tonkatsu2020 ,

Something you might want to consider is whether or not your data table is "oriented" or arranged in the right way. Normally, you'd want to have a single column with data and then the rows would be each new instance of whatever it is you're looking at.

You might consider transposing your data. Then, it's just easy to find the Col Maximum() and Col Minimum(). However, this might not be what you're after. Can you share an anonymized version of your table? That might help in understanding how your data is structured and what you want to do with it.

DS

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: how to script for finding maximum, minimum date value in a row for 100+ columns

Here is a simple formula that will find all of the continuous columns in your data table, and then calculate the maximum value for those columns. Just use it as the formula for a new column

```
If( Row() == 1,
theList = Current Data Table() << get column names( continuous )
);
Max( theLIst );
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: how to script for finding maximum, minimum date value in a row for 100+ columns

if i want to use only a range of columns, NOT all columns, how do i script this? Lets say i want to use only 10th column to 100th column for all the rows; how do i script this?

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: how to script for finding maximum, minimum date value in a row for 100+ columns

I think it'd be simple. Just create a new column with formula Min(:Col1, :Col2,....,:Coln)

You can drag and drop all columns into the formula editor.

You can drag and drop all columns into the formula editor.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: how to script for finding maximum, minimum date value in a row for 100+ columns

how to specify the range in the script, what exact symbols/characters to use in the script? that's what i am interested in

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: how to script for finding maximum, minimum date value in a row for 100+ columns

Here's one way. Use 'Help > Scripting Index' and the 'JSL Scripting Guide' to understand whatever is unclear.

```
NamesDefaultToHere(1);
// Make some data
nr = 100;
nc = 50;
dt = AsTable(J(nr, nc, RandomNormal(0, 1)));
dt << setName("Some Random Continuous Columns");
// Define a contiguous range of columns to consider
startColNum = RandomInteger(1, Floor(nc/2));
endColNum = RandomInteger(Ceiling(nc/2), nc);
// Get the list of columns to include in the formulas
allCols = dt << getColumnNames;
myCols = allCols[startColNum::endColNum];
// Add the required formulas
CMD = Expr(dt << NewColumn("Row Maximum from Cols "||Char(startColNum)||" to "||Char(endColNum), Numeric, Continuous, Formula(Max(colsTBD))));
SubstituteInto(CMD, Expr(colsTBD), myCols);
CMD;
CMD = Expr(dt << NewColumn("Row Minimum from Cols "||Char(startColNum)||" to "||Char(endColNum), Numeric, Continuous, Formula(Min(colsTBD))));
SubstituteInto(CMD, Expr(colsTBD), myCols);
CMD;
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: how to script for finding maximum, minimum date value in a row for 100+ columns

If you want to only use the 10th through the end of the data table, you can change the elements in the list used for the column specification. Here is a modification of the formula I previously submitted

```
If( Row() == 1,
theList = Current Data Table() << get column names( );
remove from( theList, 1, 9 );
);
Max( theLIst );
```

Documentation on List modification can be found in the Scripting Guide.

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: how to script for finding maximum, minimum date value in a row for 100+ columns

considering each column has values from each day; and i need to take the average of last 4 days data (average of 4 columns) and put this into a new column. how do i create a formula for this new column? where the references inside the formula will be changing every day?