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.
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.
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 );
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;
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.