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

ssg

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.

txnelson

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