Why is there such unexpected re-calculation of columns without formulas?I often use JMP to prototype data pipelines, calculating column values in one table, then querying data into another table based on calculation results. I noticed a behaviour that looks to me very strange and counterintuitive. Say, I have a table with three columns:
The second and the third columns are calculated as Lag ( :Column 1 ) and Col Sum ( :Column 1 ).
Now I query the rows into a new table where :Column 1 > 2. The result is:
":Lag of Column 1" and ":Column 1 Sum" get re-calculated, since the table inherited the formulas. This is understandable.
But what if I delete the formulas and update the table? Then, since I do not have formulas and I do not re-calculate values in the destination table, I will see the data as it is in the the source table, right? Guess what, wrong! Nothing changes. I still see null, 3 and 7, 7 not 2, 3 and 10, 10.
Is this a defect? Or is this as expected? How can this be explained?
Thanks!
Automation and ScriptingRe: Why is there such unexpected re-calculation of columns without formulas?Ouch, a surprisingly toxic mixture: Query Builder and Column formulas!
Some settings like in Subset
would remove lot of headache.
Besides that: a nice magnifying glass for the nuts and bolts of JMP : )
Which version of JMP? Can you share the steps from the log?
I tested it with JMP 18.2.2 and 19.0, where Lag() gets executed after executing the query, Col Sum() doesn't.
After removing the formulas, everything is fine.
[maybe: did you reference an old data table "untitled" - with formulas?]
names default to here(1);
dt = New Table( "Untitled",
Add Rows( 4 ),
New Column( "Column 1",
Format( "Best", 12 ),
Set Values( [1, 2, 3, 4] )
),
New Column( "Sum[Column 1]",
Formula( Col Sum( :Column 1 ) )
),
New Column( "Lag[Column 1]",
Formula( Lag( :Column 1, 1) )
)
);
dt << run formulas();
wait(0);
queryDT= Expr(New SQL Query(
Connection( "JMP" ),
Select(
Column( "Column 1", "t1" ),
Column( "Sum[Column 1]", "t1" ),
Column( "Lag[Column 1]", "t1" )
),
From( Table( "Untitled", Alias( "t1" ) ) ),
Where(
GT(
Column( "Column 1", "t1" ),
2,
UI( Comparison( Base( "Continuous" ) ) )
)
)
) << Run Foreground);
queryDT;
Column (dt, 2) << delete formula();
Column (dt, 3) << delete formula();
wait(0);
queryDT;
wait(0);
dt << set name("input");
Re: Why is there such unexpected re-calculation of columns without formulas?Are you making the query to JMP data table? Are you removing formulas from which table? Can you share example datasets to replicate the issue?
Re: Why is there such unexpected re-calculation of columns without formulas?Yes, the second table in the second screenshot is a result of the querying first JMP data table in the first screenshot. Tables are attached - place them in the same folder and execute "Update From Database" script in the second one. The second table does not have formulas; yet, the results are as if the formulas existed and got evaluated.
Re: Why is there such unexpected re-calculation of columns without formulas?JMP Pro V.17
Tables are attached.
Re: Why is there such unexpected re-calculation of columns without formulas?Not a nice workaround, but you can suppress formula eval temporarily from the original table, run the query and re-enable formula evaluation (this should in my opinion already happen by Query Builder).
Also, I would contact JMP support about this as there is some weirdness going on, JMP Support
Re: Why is there such unexpected re-calculation of columns without formulas?suggestion for JMP 19.1:
Query Builder: disable formula column
Re: Why is there such unexpected re-calculation of columns without formulas?Ah, I misinterpreted the
But what if I delete the formulas and update the table?
OK, still the same in JMP19.
So, JMP runs the query (including the formulas) - and replaces the data in the target table with the new results?
This is actually what I expected with 'Update'. But why can't JMP do such a 'keep the table, just update the data' update if a new column has been added in the meantime? Concerning Column4, it looks more like it's deleting everything that was there and replacing it with the new query results.
Maybe: it takes the query result - but copies and applies the old column settings?
... thereby removing the column formula - and hiding the issue?
Re: Why is there such unexpected re-calculation of columns without formulas?Yes, this workaround works. Another clumsy workaround I use is to query all rows into an intermediate table without formulas and then query this intermediate table.
Re: Why is there such unexpected re-calculation of columns without formulas?What was the response from JMP Support?
Re: Why is there such unexpected re-calculation of columns without formulas?I did not contact them. I'd rather count on this community and myself 
Re: Why is there such unexpected re-calculation of columns without formulas?Hm, I prefer an official fix vs. "workarounds".
get it fixed - and forget about it.
find a workaround and don't forget!!
- and don't forget to tell every colleague!
Re: Why is there such unexpected re-calculation of columns without formulas?Concerning "community and myself" - I added the topic to
Caution: Places where Jmp does something unexpected
On the other hand, I am quite confident that this issue can be resolved in the GUI.
Options similar to those in Subset could help the customer to understand the issue, and the default option should be to remove or disable the formula column.
Let's see if this behavior is considered a bug: TS-00244891.
If not, it will take longer, as we first need to collect enough Kudos on the wish list: Query Builder: disable formula column
