topic Re: Evaluating a variable column list as a string and inserting into a new column formula in Discussions

Evaluating a variable column list as a string and inserting into a new column formula

MuttonChops — Wed, 24 Apr 2019 01:28:51 GMT

I've created a variable that is a string of column names. This is inside a FOR loop so it will change with each loop.

Here is a sample table for demonstration, in reality my table is about 400 columns with weird names. I'm trying to group columns that should be grouped and report the max value. 99.9% of the time there will only be 1 value per row for each 'like' column that is to be grouped.

Col1	Col2	Col3
22
	22
		32
	32
1
		56
	34

This first part is a loop that generates a variable column list. I don't have all the code here but here is a summary. This works so I don't want to bog down the discussion in this.

VarCols = functional code here and the output is a string with this format: {"col1", "col2", "col3"}

Then my code is supposed to create a new column named 'VarName' (defined in another part of the loop) then insert a formula that reports the max value of whichever columns are in VarCols. But I just can't get it to evaluate. Am I doing this way wrong?

DT << New Column(VarName, Numeric, "Continuous", formula(Maximum(columns(eval(VarCols)))));

Here is the specific error though i inserted the variable column name to avoid confusion:

Column VarName Formula Interrupted
Name Unresolved: columns 1 times At rows: 2 Operation: columns, columns( Eval( VarCols) ) /*###*/
Formula evaluation errors have been ignored

Re: Evaluating a variable column list as a string and inserting into a new column formula

txnelson — Wed, 24 Apr 2019 03:32:37 GMT

Another community member may have a more efficient piece of code, but here is how I would code your request

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );
VarCols = {"NPN1", "PNP2", "NPN2"};

VarName = "NewCol";

// Create the JSL required to generate the new column
theExpr = "DT << New Column(VarName, Numeric, Continuous, formula(Maximum(:" || VarCols[1];
For( i = 2, i <= N Items( VarCols ), i++,
	theExpr = theExpr || ",:" || VarCols[i]
);
theExpr = theExpr || ")));";
// Run the generated JSL
Eval( Parse( theExpr ) );

Re: Evaluating a variable column list as a string and inserting into a new column formula

MuttonChops — Wed, 24 Apr 2019 14:50:59 GMT

That worked, thanks @txnelson

Re: Evaluating a variable column list as a string and inserting into a new column formula

SimFred — Fri, 18 Nov 2022 05:16:38 GMT

Long time fan. First time I actually need to post to get a question answered.

This is great. I just managed to get to that same point in my code. The next step is to inser this string into a function in a new column.

dt << New Column( "MinValue", numeric, continuous, Formula( min(Eval( Parse( TheExpr)))));

but that is not working for me. Any help would be appreciated.

Please ignore. Brain was not working right late tonight...

Re: Evaluating a variable column list as a string and inserting into a new column formula

txnelson — Fri, 18 Nov 2022 05:51:15 GMT

Please provide the value of "TheExpr"