topic Re: Column Formulas to slow : how to wait until they are done calculating in Discussions

Column Formulas to slow : how to wait until they are done calculating

justvince — Thu, 07 Mar 2019 17:49:51 GMT

Greetings, I have run into situations where I import raw data into a data table (dt1), and then open another data table that has say spec limits (dt2). The spec limits table has an USL and a LSL only. I insert a new calcultated column into dt2 that is the target spec limit (mean(USL,LSL)). I then loop through all rows of my raw data dt1 and if I get a match of the name of the test and spec limits, then set colum properties with the spec limits (e.g., col << Set Property( "Spec Limits", {LSL( myLSLx ),USL( myUSLx ),Target( mytargetx ), );)... Then close spec file dt2, no save.

I think the problem I am having is that the calculated column (dt2:_Target) is not fast enough, and I get all empty values when trying to read them, right after creating the new calculated column("_Target"...). If I keep the dt2 file open, I see the correct values in the calculated column "_Target". I have played with the dt2<< Begin data Update; and that does not work either, but maybe I am putting that in an incorrect location in the script? The only work around I have, is to not use a formula for the new column, but to calculate in a script the target value and place it in the spec file by indexing through all the rows.

The root of my question is, can I wait for all the columns to be completed with calculations before trying to read a specific value in a row/column?

Does not work

E.g,

New Column( "_Target",Numeric,"Continuous",Format( "Best", 12 ),Formula(If( Is Missing( :_LSL ) | Is Missing( :_USL ),
    Empty(),
Mean( :_LSL, :_USL )
)

Workaround

E.g.,

dt2 = current data table();
New Column( "_Target", Numeric, "Continuous");
dt2_rows = nrows();
for (i=1, i<=dt2_rows, i++,
if(or(!is missing(dt2:_LSL[i]),!is missing(dt2:_USL[i])),
   dt2:_Target[i] = ((dt2:_LSL[i]) + (d2t:_USL[i])) / 2;
  ,
  );

Re: Column Formulas to slow : how to wait until they are done calculating

ian_jmp — Thu, 07 Mar 2019 17:49:24 GMT

Try sending your table a 'run formulas' message:

dt << runFormulas;

Re: Column Formulas to slow : how to wait until they are done calculating

txnelson — Thu, 07 Mar 2019 17:54:45 GMT

Below is a simple modification that should work for you. First, by getting rid of the If() clause it will speed up the processing, and secondly, the

dt << rerun formulas;

will force JMP to complete processing on the formulas before continuing

dt = Current Data Table();
dt << New Column( "_Target",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( (:LSL + :USL) / 2 )
);
dt << rerun formulas;

Re: Column Formulas to slow : how to wait until they are done calculating

justvince — Thu, 07 Mar 2019 20:47:15 GMT

Awesome! It works!

Re: Column Formulas to slow : how to wait until they are done calculating

justvince — Thu, 07 Mar 2019 20:47:17 GMT

NIce, This works too. The rerun formulas is not in the JMP V13 scripting guide, while run formulas is. Both work in this case.

Re: Column Formulas to slow : how to wait until they are done calculating

Craige_Hales — Thu, 07 Mar 2019 21:35:41 GMT

Was not aware of "rerun" either. Looks like it makes the formulas re-evaluate, whether they need it or not. If you use random numbers in your formulas, you can see the difference. I'd recommend <<runFormulas for most cases.

Re: Column Formulas to slow : how to wait until they are done calculating

Mark_Bailey — Fri, 08 Mar 2019 12:41:02 GMT

You are correct that this message is not described in the Scripting Guide. It is described in the JSL Syntax Guide. The first book is not a complete description of every function and message in JSL but the second book should be complete. The two books are meant to complement each other and to be used together.

The message is also covered by the Scripting Index, with a link to the help for it. A search for 'formula' would return a list including the << Run Formulas and << Rerun Formulas messages.

Re: Column Formulas to slow : how to wait until they are done calculating

gzmorgan0 — Tue, 12 Mar 2019 10:20:16 GMT

This is a simple "pile on" comment. @txnelson's formula will run fatser than the one you provided. Column formulas run faster than a for loop. Also, the conditional statement is not required, since JMP returns an empty() in any calculation that has an empty value. Thus, (:USL + :LSL)/2 will be empty if any one or both values are empty.

This is just an FYI.