JSL Script to change column name in formula with real formula from the corresponding column

Question

Hi everyone

I try to automate the process where I want to change the column name in one formula (i.e. "Lower 90% Indiv TiterHPLCProtA@D14"  with the 'real' formula of this column (second picture).

I want to repeat this process for all column names within the first picture.

The script would need something like:

* Find all column names within the first formula
* Start with the first column name
* Find the corresponding column within the data table and copy the formula
* replace column name in the first formula with the column formula
* repeat this process until all column names in the first formula have been replaced with their "real" formula

First column formula

one example of the formula I want to insert in the first column formula, by replacing "[...]TiterHPLCProtA[...]"

All the best!

/Marco

pauldeen · Accepted Answer

You can do this with some advanced scripting techniques:

You will have to follow the column name to the column,

Formula_expression = Column << get formula

Then you can substitute the column references with the formula expression.

So look into expr(), name expr(), and substitute into().

If the only reason you do this is for the use in Profiler, you can just turn on the check box here:

ih · Accepted Answer

As @pauldeen pointed out you can profile this using expand intermediate formulas, but you can go one step further and use the save expanded formulas formulas option in that profiler to resolve all of those references in a new column formula.

For example:

Names Default To Here( 1 );

dt = Open( "$Sample_data/iris.jmp" );

dt << New Column("Log[Petal width]", Numeric, "Continuous", Format("Best", 12), Formula(Log(:Petal width)));
dt << New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Formula(:Sepal length + :Sepal width + :"Log[Petal width]"n));

prof = Profiler(
	Y( :Column 1 ),
	Profiler(
		1,
		Term Value(
			Sepal length( 6, Lock( 0 ), Show( 1 ) ),
			Sepal width( 3.125, Lock( 0 ), Show( 1 ) ),
			Petal width( 1.269, Lock( 0 ), Show( 1 ) )
		)
	),
	Expand
);

col = prof << Save Expanded Formulas;

If you want to do this by scripting you have several options.  I would start with a for loop that would look for a column reference, check if that column has a formula, and then use the substitute command to insert it.  Give it a try and post it here if you get stuck.