topic Re: JSL Script to change column name in formula with real formula from the corresponding column in Discussions

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

Marco_ — Fri, 09 Jun 2023 15:58:27 GMT

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

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

pauldeen — Wed, 14 Sep 2022 09:04:07 GMT

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:

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

ih — Fri, 16 Sep 2022 12:33:01 GMT

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.

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

Marco_ — Tue, 20 Sep 2022 07:22:47 GMT

Thanks a lot to both of you! That was exactly what I was looking for!