cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
View Original Published Thread

How does JMP evaluate a column formula?

hogi
Level XII

@txnelson just used summarize() inside as constant:

https://community.jmp.com/t5/Discussions/Alphabetical-Ranking-of-Nominal-Data/m-p/822725/highlight/t... 

As Constant( Summarize( theLevels = by( :age ) ) );
Contains( theLevels, Char( :age ) );

 

Very interesting approach!

For as constant, the scripting index provides the example

Names Default To Here( 1 );
New Table( "As Constant Demo Table 1",
	Add Rows( 10 ),
	New Column( "Non-Constant", Formula( Random Uniform() ) ),
	New Column( "Constant", Formula( As Constant( Random Uniform() ) ) )
);

this is easy to understand - As Constant( Random Uniform() ) is executed just once and the return value is used for the other rows.
The thing that puzzled me: The return value of Summarize is empty and actually, it's theLevels which we are interested in.

So, the actual trick is
as constant:  "is executed just once"
very useful!

Could be used for:

as constant("JMP just started to evaluate column xyz");



Let's use this trick for another application: to open an auxiliary data table.

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

dt << New Column( " col",
	Formula(
	as constant (
			dt = Current Data Table();
			dtsum = dt << summary( Group( :age, :height ) );
			nr = N Rows( dt );
		);

		Eval( (EvalExpr(myrows= dtsum << get rows where( :age == Expr(dt:age[row()])))));
		Print(:age, myrows);
		dtsum[myrows, 2]
	)
);

But it's not as easy as I expected ....

 

Hm, it's quite late - I don't have any idea why:

hogi_0-1734214055725.png

6 REPLIES 6
hogi
Level XII


Re: How does JMP evaluate a column formula?

The next question - and therefore the title of the post:
why does JMP delete the entries when I close dtsum?

RankUnique_ByGroup.mp4
Video Player is loading.
Current Time 0:00
Duration 0:00
Loaded: 0%
Stream Type LIVE
Remaining Time 0:00
 
1x
    • Chapters
    • descriptions off, selected
    • captions off, selected
      (view in My Videos)

       

       

      Try(close(dt, nosave));
      dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
      
      Data Table( "Big Class" ) << Sort(
      	By( :age, :height ),
      	Replace Table
      );
      
      dt << New Column( " col",
      	Formula(
      	local({tmp,myAge},
      	as constant (
      			dt0 = Current Data Table();
      			dtsum = dt << summary( Group( :age, :height ) );
      			//dtsum:age << set name(age2);
      			nr = N Rows( dt0 );
      		);
      		myage = dt:age[row()];
      		Eval (Eval Expr(myrows = dtsum << get rows where( :age == Expr(myAge))));
      		Print(myRows);
      		tmp = contains(dtsum[myrows,2], :height);
      		if(row()==nr,  dt<< runformulas();wait(1);close(dtsum, noSave);Caption("done"));
      		tmp
      )));
      hogi
      Level XII


      Re: How does JMP evaluate a column formula?

      Issue 1 can be fixed via:

      dt << New Column( " col",
      Expression,
      	set each value (
      	local({tmp,myAge},
      	as constant (
      			dt0 = Current Data Table();
      			dtsum = dt << summary( Group( :age, :height ) );
      			//dtsum:age << set name(age2);
      			nr = N Rows( dt0 );
      		);
      		myage = dt:age[row()]; // save as variable and use later here  ↘
      		Eval (Eval Expr(myrows = dtsum << get rows where( :age == Expr(myAge))));
      		dtsum[myrows,2]
      )));
      hogi
      Level XII


      Re: How does JMP evaluate a column formula?

      Wow, and the second one is very surprising, it can be fixed via

      //dense rank - with groupby
      
      dt << New Column( " col",
      	set each value( // <- the first change is here 
      	local({tmp,myAge},
      	as constant (
      			dt = Current Data Table();
      			dtsum = dt << summary( Group( :age, :height ) );
      			//dtsum:age << set name(age2);
      			nr = N Rows( dt );
      		);
      		myage = dt:age[row()];
      		Eval (Eval Expr(myrows = dtsum << get rows where( :age == Expr(myAge))));
      		tmp = contains(dtsum[myrows,2],dt:height); // <- the second change is here : use dt:height instead of :height to force JMP to use the right table!
      		if(row()==nr, close(dtsum, noSave));
      		tmp
      )));

      This also fixes the issue with several "0" entries in the previous approach.

      So, under the line, the questions was not:
      why does JMP delete the values in dt if table dtsum is closed?

      the actual question was:
      why does JMP use dtsum:height instead of dt:height when calculating entries for the data table dt?

      open question: why does dt:height just fix the issue for set each value() and not for formula()

      hogi
      Level XII


      Re: How does JMP evaluate a column formula?

      Ah,

      dt << New Column( "dense rank_ grouped by",
      Character, 
      	formula (
      	local({tmp,myAge},
      	as constant (
      			dt = Current Data Table();
      			dtsum = dt << summary( Group( :age, :height ) );
      			//dtsum:age << set name(age2);
      			nr = N Rows( dt );
      		);
      		rw = row();
      		myage = dt:age[row()];
      		Eval (Eval Expr(myrows = dtsum << get rows where( dtsum:age == Expr(myAge)))); // <- use dtsum:age !!!
      		tmp = contains(dtsum[myrows,2], dt:height[rw]); // <- use dt:height !!!!
      		if(row()==nr, close(dtsum, noSave));
      		tmp
      
      )));



      @jthi , I remember, you had a similar issue some while ago - a conflict if two tables have overlapping column names.

       

      I will add a link when I find it ...

      jthi
      Super User


      Re: How does JMP evaluate a column formula?

      hogi
      Level XII


      Re: How does JMP evaluate a column formula?

      Ah, right, this one.
      So, it's not only << set each value() which is affected - formula() can be affected as well.

       

      How fortunate that I could not find your post yesterday. 
      I wouldn't have dared << set each value() after reading it ...
      maybe: not as a workaround but as a crosscheck.

       

      Interesting: In my case, there is no col statistic involved (like col rank()).

      Maybe JMP knows that the code was intended to create one ; )