topic Re: JSL for converting a formula to its results in Discussions

JSL for converting a formula to its results

MFVIT — Fri, 09 Jun 2023 19:45:00 GMT

Hello,

I have the following jsl code

dt << New Column( "I (%)", (:Mean * 100) );

that takes a column (Mean), multiplies by 100 and create a new column called I(%) with the formula.

I do not want the formula but just the result of the calculation in the new column. How can I do this?

Many thanks.

Re: JSL for converting a formula to its results

ian_jmp — Thu, 22 Apr 2021 09:59:49 GMT

NamesDefaultToHere(1);

// Sample table
dt = Open("$SAMPLE_DATA/Big Class.jmp");

// Add a formula column
dt << NewColumn("BMI", Numeric, Continuous, Formula(:weight/(:height^2)));

// Make sure the formula is run
dt << runFormulas;

// Wait a little, then remove the formula
Wait(3);
Column(dt, "BMI") << deleteFormula;

Re: JSL for converting a formula to its results

Craige_Hales — Thu, 22 Apr 2021 11:51:12 GMT

The wait(3) is just so you can watch it; it is not required after <<RunFormulas.

You can also use the formula editor to clear the formula for the same effect.

Re: JSL for converting a formula to its results

jthi — Thu, 22 Apr 2021 12:51:39 GMT

Try using <<Set Each Value() instead of Formula()

Re: JSL for converting a formula to its results

Mark_Bailey — Thu, 22 Apr 2021 12:56:32 GMT

You do not need a formula at all. I recommend not using column formulas when scripting unless you want the values to dynamically change after the script is done. So your case could be solved this way:

dt << New Column( "I (%)", Numeric, Continuous, Values( (:Mean << Get Values) * 100) );

Re: JSL for converting a formula to its results

Craige_Hales — Thu, 22 Apr 2021 13:02:14 GMT

Cool!

Re: JSL for converting a formula to its results

Mark_Bailey — Thu, 22 Apr 2021 14:09:11 GMT

That message is not helpful in this case. It is used to set every row in the target column to the same constant value.

Re: JSL for converting a formula to its results

txnelson — Thu, 22 Apr 2021 14:26:31 GMT

Hee is a simple example using Set Each Value

names default to here(1);

dt=open("$SAMPLE_DATA\big class.jmp");

dt << new column("times100", numeric, continuous, set each value(:height*100));

Re: JSL for converting a formula to its results

Craige_Hales — Thu, 22 Apr 2021 15:22:11 GMT

Cooler! I'd assumed each value had to be set to the same value.

Re: JSL for converting a formula to its results

Mark_Bailey — Thu, 22 Apr 2021 17:19:58 GMT

That behavior is the intended purpose of the << Set Each Value message as it is documented.

Re: JSL for converting a formula to its results

jthi — Thu, 22 Apr 2021 19:26:11 GMT

This maybe gets a bit out of topic and I can create different post to discuss this further...

Even though the documentation is what it is for Set Each Value (JMP documentation isn't always exactly on point...) I feel like it is the easiest way the way to handle cases like this. You can easily use complicated "formulas" with Set Each Value without worrying about removing the formulas later. With Values() the calculations might get a bit more difficult to do, see my example (I'm definitely not proficient with JSL Matrices so there is most likely way easier way to do what I did in my example).

Example with Formulas, Set Each Value and Values:

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

//With formulas
newCol = dt << New Column("Formula_simple", Numeric, Continuous, Formula(:height*:weight));
newCol << Delete Formula;
newCol = dt << New Column("Formula_ColMin", Numeric, Continuous, Formula(:height*ColMin(:weight, :sex)));
newCol << Delete Formula;

//With Set Each Value
newCol = dt << New Column("SetEach_simple", Numeric, Continuous, Set Each Value(:height*:weight));
newCol = dt << New Column("SetEach_ColMin", Numeric, Continuous, Set Each Value(:height * ColMin(:weight, :sex)));

//With Values
newCol = dt << New Column("Values_simple", Numeric, Continuous, Values((:height << get values) :* (:weight<< get values)));
newCol = dt << New Column("Values_ColMin", Numeric, Continuous, Values(
	Summarize(exg = By(:sex), exm = Min(:weight)); //get min values by group
	m_val = J(N Rows(dt),1,1);//matrix for values
	height_vec = :height << get values;
	For(i = 1, i <= N Items(exg), i++, //loop to "fill" matrix for values
		tempRows = Loc(:sex << get as matrix, exg[i]);
		m_val[tempRows] = height_vec[tempRows] * exm[i];
	);
	m_val;
));

What does Set Each Value do behind the scenes because it seems to work just fine? Should the documentation maybe be updated to include this behavior or new function created to do this? It could break quite many scripts people have if Set Each Values behavior were to be changed.

Re: JSL for converting a formula to its results

MFVIT — Mon, 26 Apr 2021 08:06:16 GMT

Thank you very much, very useful.

Re: JSL for converting a formula to its results

MFVIT — Mon, 26 Apr 2021 08:06:33 GMT

Thanks! Very clear.

Re: JSL for converting a formula to its results

MFVIT — Mon, 26 Apr 2021 08:06:52 GMT

Many thanks.