JSL to create formula column with variable column names

RonB — Fri, 15 Dec 2023 12:35:31 GMT

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

Re: JSL to create formula column with variable column names

txnelson — Fri, 15 Dec 2023 13:20:35 GMT

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

Re: JSL to create formula column with variable column names

RonB — Sun, 17 Dec 2023 08:18:04 GMT

Many thanks!

topic JSL to create formula column with variable column names in Discussions

JSL to create formula column with variable column names

Re: JSL to create formula column with variable column names

Re: JSL to create formula column with variable column names