BookmarkSubscribe
Choose Language Hide Translation Bar
justvince
Community Trekker

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

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;
  ,
  );

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
ian_jmp
Staff

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

Try sending your table a 'run formulas' message:

dt << runFormulas;
7 REPLIES 7
ian_jmp
Staff

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

Try sending your table a 'run formulas' message:

dt << runFormulas;
justvince
Community Trekker

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

Awesome! It works!
0 Kudos
txnelson
Super User

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

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;
Jim
justvince
Community Trekker

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

NIce, This works too. The rerun formulas is not in the JMP V13 scripting guide, while run formulas is. Both work in this case.
0 Kudos
Craige_Hales
Staff (Retired)

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

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.

Craige
0 Kudos
Highlighted

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

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.

Learn it once, use it forever!
gzmorgan0
Super User

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

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.