JSL to create formula column with variable column names

[#1]

Hi,

I need to create a new formula column via script using variables for column names and constant.

Tried various combinations of Expr, Eval, Parse, Eval Insert for no avail.

See sample.

Thanks

row = 11;
col = "foo";

for (i = 1, i <= ncols(dt), i++,
dt << New Column( "ratio " || char(i), numeric,
formula(( column(col) - column(col)[row] ) / (Col Max(col) - Col Min(col))));
);

[#2 · Accepted solution]

Using

for (i = 1, i <= ncols(dt), i++,

when you are adding a new column within the For() loop results in the loop never coming to an end.  The ncols(dt) increases by 1 with each loop, and therefore i will never reach ncols(dt);

The formula() element of a New Column() function does not parse it's contents as it is created for the column.  It only parses it's contents when the formula is run for each row.  Therefore, the col and row references can cause issues when those values are changed.  The proper way to handle this, is to substitute into the formula, the actual values for col and row, before the formula is created.

Names Default To Here( 1 );
dt = 
// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "$SAMPLE_DATA/Big Class.jmp" );

row = 11;
col = "height";
stop = n cols(dt);

For( i=1, i<= stop, i++,
	Eval(
		Substitute(
				Expr(
					dt << New Column( "ratio " || Char( i ),
						numeric,
						formula( (As Column( _col_ ) - As Column( _col_ )[_row_]) / (Col Max( As Column( _col_ ) ) - Col Min( As Column( _col_ ) )) )
					)
				),
			Expr( _col_ ), col,
			Expr( _row_ ), row
		)
	);
);

[#3]

Many thanks!