topic Re: Col Median - what's wrong? in Discussions

Col Median (actually: Substitute) - what's wrong?

hogi — Fri, 09 Jun 2023 16:02:57 GMT

I don't understand what's wrong with this file.

height_rel and height_rel 2 have exactly the same Formula.

But in height_rel 2 the values are missing.

Before saving the file, the Formula editor complained that there were too few arguments in the Col Median funktion.

One could convince the Formula Editor that everything is OK by double-clicking on the formula editor and then closing all windows by clicking on OK, Ok, Ok.

After saving the file, this is not possible. I did not find a way to revive the formula.

If you copy and paste the column, the copy is OK.

Re: Col Median - what's wrong?

jthi — Wed, 23 Nov 2022 18:23:50 GMT

Try going back to formula editor and press Apply. Or check Suppress Eval -> Apply -> uncheck Suppress Eval -> Apply again. You can also try Current Data Table() << Rerun formulas.

Re: Col Median - what's wrong?

hogi — Thu, 24 Nov 2022 06:44:29 GMT

Many thanks - 3 ways to fix my problem :)

Just via "OK" it didn't work.

Digging deeper - why did it happen ...

Is there a reason why myFormula3 (with substitution of Head and Aggregation) and myFormula4 (with subsequent substitution of Head and Aggregation) don't work? myFormula1 and myFormula2 (just substituting one of both) do.
(same for Jmp 16.2 and Jmp 17)

name Expr(myFormulax) look quite the same:

Name Expr(myFormula1) = Divide(:height, Col Median(:height));
Name Expr(myFormula2) = :height / Col Median(:height);
Name Expr(myFormula3) = Divide(:height, Col Median(:height));
Name Expr(myFormula4) = Divide(:height, Col Median(:height));

Unfortunately, before saving an re-opening the file none of the above 3 solutions work :(

Would be nice if a Current Data Table() << Rerun formulas; could solve the problem.

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

myFormula1 = Expr(__myHead__( :height,Col Median( :height ) ));
Substitute Into( myFormula1,
	Expr( __myHead__ ), Expr( Divide ),
					
);

myFormula2 = Expr(Divide( :height, __aggregation__( :height ) ));
Substitute Into( myFormula2,
	Expr( __aggregation__ ),Expr( Col Median )
					
);

myFormula3 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula3,
	Expr( __myHead__ ), Expr( Divide ),
	Expr( __aggregation__ ),Expr( Col Median )					
);

myFormula4 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula4,Expr( __myHead__ ), Expr( Divide ));
Substitute Into( myFormula4,Expr( __aggregation__ ),Expr( Col Median ));


Show( Name Expr( myFormula1 ) );
Show( Name Expr( myFormula2 ) );
Show( Name Expr( myFormula3 ) );
Show( Name Expr( myFormula4 ) );

Eval( Substitute( Expr( New Column( "new1", Formula( __f__ ) ) ), Expr( __f__ ), Name Expr( myFormula1 ) ) );
Eval( Substitute( Expr( New Column( "new2", Formula( __f__ ) ) ), Expr( __f__ ), Name Expr( myFormula2 ) ) );
Eval( Substitute( Expr( New Column( "new3", Formula( __f__ ) ) ), Expr( __f__ ), Name Expr( myFormula3 ) ) );
Eval( Substitute( Expr( New Column( "new4", Formula( __f__ ) ) ), Expr( __f__ ), Name Expr( myFormula4 ) ) );

Re: Col Median - what's wrong?

hogi — Sun, 27 Nov 2022 09:41:45 GMT

What's wrong with these:

myFormula3 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula3,
	Expr( __myHead__ ), Expr( Divide ),
	Expr( __aggregation__ ),Expr( Col Median )					
);

myFormula4 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula4,Expr( __myHead__ ), Expr( Divide ));
Substitute Into( myFormula4,Expr( __aggregation__ ),Expr( Col Median ));

Re: Col Median - what's wrong?

jthi — Sun, 27 Nov 2022 10:52:49 GMT

I have "solved" this type of issue earlier at work, but I cannot remember anymore how and why it happened. Also haven't recorded it to my tips&tricks list, so cannot find the reasoning and solution easily. The second expression seems to be getting stuck if it is inside other expression when built like this.

One workaround:

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

myFormula3 = Expr(__myHead__(:height, __aggregation__(:height)));
Substitute Into(myFormula3, Expr(__myHead__), Expr(Divide), Expr(__aggregation__), Expr(Col Median));

myFormula4 = Expr(__myHead__(:height, __aggregation__(:height)));
Substitute Into(myFormula4, Expr(__myHead__), Expr(Divide));
Substitute Into(myFormula4, Expr(__aggregation__), Expr(Col Median));

Show(Name Expr(myFormula3));
Show(Name Expr(myFormula4));

new_col = Eval(
	Substitute(
		Expr(New Column("new3", Formula(__f__))),
		Expr(__f__), Parse(Char(Name Expr(myFormula3)))
	)
);

Re: Col Median - what's wrong?

hogi — Tue, 27 Jun 2023 21:01:05 GMT

I feared that I would need some nested If statements and many times the same code again to make it work.

Very cool, "dirty" workaround - thanks ;)

... I wish one didn't need it:

edit: not needed

@jules : Is there a webinar which explains why the above code just works with a Parse(Char))?

Re: Col Median - what's wrong?

jthi — Sun, 27 Nov 2022 14:29:55 GMT

I don't think the issue is with Substitute but rather with something else. Maybe JMP doesn't understand that Col Median is supposed to be function in this case as the replacement is a bit weird (brackets are missing). Here is most likely "correct" way to do this (note () after Divide and Col Median).

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

myFormula3 = Expr(__myHead__(:height, __aggregation__(:height)));
Substitute Into(myFormula3, Expr(__myHead__), Expr(Divide()), Expr(__aggregation__), Expr(Col Median()));
show(Name Expr(myFormula3));

new_col1 = Eval(
	Substitute(
		Expr(New Column("new3", Formula(__f__))),
		Expr(__f__), Name Expr(myFormula3)
	)
);

Re: Col Median - what's wrong?

hogi — Sun, 27 Nov 2022 15:23:24 GMT

Wow, important finding!
To be honest, I did not know that one has to use the brackets - I wouldn't even have bet that it works.

Thanks a lot, I definitely will add a bookmark for this post to my tips&tricks list :)

Re: Col Median - what's wrong?

jules — Sun, 27 Nov 2022 15:25:46 GMT

I don't know of any webinar that covers that (or even discusses this issue), but it's a clever workaround to force JMP to parse that expression again. I'm really impressed with @jthi's most recent solution of including the () in the substitution, I would not have guessed that would work but it seems to help JMP recognize that you're passing in a function and it handles the rest. Surprising and interesting solution to what is a mysterious and confusing issue!

Re: Col Median - what's wrong?

jules — Sun, 27 Nov 2022 15:49:19 GMT

Just to add a bit of extra mystery, it appears only one of the substituted functions needs to include the "()" so both of the formulas below will work.

Edit: And upon further digging, it seems that JMP will tolerate only 1 function in an expression passed without the "()" in the substitution. Three more formulas are at the bottom where I wrap another function around your existing expression, and it will work correctly with any two functions substituted with the parentheses. I suspect there's an internal pass JMP does to make inferences about an expression in order to make it legal and it's only going to make one adjustment and then break.

myFormula3 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula3,
	Expr( __myHead__ ), Expr( Divide() ),
	Expr( __aggregation__ ),Expr( Col Median )					
);

myFormula4 = Expr(__myHead__( :height, __aggregation__( :height ) ));
Substitute Into( myFormula4,
	Expr( __myHead__ ), Expr( Divide ),
	Expr( __aggregation__ ),Expr( Col Median() )					
);

myFormula5 = Expr(__top__(__myHead__( :height, __aggregation__( :height ) )));
Substitute Into( myFormula5,
	Expr(__top__), Expr(Log),
	Expr( __myHead__ ), Expr( Divide),
	Expr( __aggregation__ ),Expr( Col Median )					
);

myFormula6 = Expr(__top__(__myHead__( :height, __aggregation__( :height ) )));
Substitute Into( myFormula6,
	Expr(__top__), Expr(Log()),
	Expr( __myHead__ ), Expr( Divide()),
	Expr( __aggregation__ ),Expr( Col Median )					
);

myFormula7 = Expr(__top__(__myHead__( :height, __aggregation__( :height ) )));
Substitute Into( myFormula7,
	Expr(__top__), Expr(Log()),
	Expr( __myHead__ ), Expr( Divide),
	Expr( __aggregation__ ),Expr( Col Median() )					
);

Re: Col Median - what's wrong?

hogi — Fri, 11 Aug 2023 20:02:35 GMT

related community discussion: