Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Column Formulas to slow : how to wait until they are done calculating

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 7, 2019 9:44 AM
(801 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Try sending your table a 'run formulas' message:

`dt << runFormulas;`

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Try sending your table a 'run formulas' message:

`dt << runFormulas;`

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Awesome! It works!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.