How to find max/min values from columns with certain keyword in the column names

[#1]

Hi, I am trying to process data sets to find the max values of certain columns(based on certain keyword in the column names). The number of columns may differ for each data set, but the column name "keywords" will remain the same. Trying to create a script that could work on different data sets. Below is a simple example of a data table to illustrate it:

Row	data1_volt_grp1	data1_volt_grp2	data2_volt_grp1	data2_volt_grp2	data_current_A_grp1	data_current_A_grp2	data2_current_A_grp1	data2_current_A_grp2	max_volt_grp1	max_current_grp2
A	0.5	0.8	0.7	0.2	1.2	1.4	0.9	1.8	0.7	1.8
B	0.6	0.9	0.3	0.4	1	1.1	1.5	0.8	0.6	1.1

1. Need to find max values of columns with column name consisting  volt & grp1 in the naming. And populate this data in new column "max_volt_grp1".

2. Need to find max values of columns with column name consisting  current & grp2 in the naming. And populate this data in new column "max_current_grp2".

3. The outcome expected are on the last 2 columns. 

Thanks.

[#2]

Here is how I would do this to create column Max_volt_grp

Names Default To Here( 1 );
dt = Current Data Table();
colNames = dt << get column names( string );
valueMatrix = [];
//col = "data1_volt_grp1"
For Each( {col}, colNames,
	If( Contains( col, "volt" ) & Contains( col, "grp1" ),
		valueMatrix = valueMatrix |/ Matrix( Column( col ) << get values )
	)
);
If( Length( valueMatrix ) > 0,
	dt << New Column( "Max_volt_grp1" );
	:Max_volt_grp1[1] = Max( valueMatrix );
);

[#3]

Hi Jim, 

The solution gets the max value on those column for the entire number of rows. I am looking for the script to have the column max value populated for each row separately (the number of rows will also differ based on the dataset). Expecting output for each row as in the highlighted column. Thanks for your help.

[#4 · Accepted solution]

My mistake, try this method.  I don't like using this Eval(Parse()) methodology, but it was the only way I could make it work.

The code creates a character string that contains the JSL required to create the new column, and then runs the JSL

Names Default To Here( 1 );
dt = Current Data Table();
colNames = dt << get column names( string );

// Build a list of the columns to be evaluated
theCols = "";
For Each( {col}, colNames,
	If( Contains( col, "volt" ) & Contains( col, "grp1" ),
		theCols = theCols || ", :" || col
	)
);

// Create the new column and formula command, adding in the list of
// columns ceated in the above loop and then executing it
Eval(
	Parse(
		"dt << new column(\!"max_volt_grp1\!",formula(Max(" ||
		Substr( theCols, 2 ) || ")));"
	)
);

[#5]

Thanks Jim. The scripts works as expected.