Why is there such unexpected re-calculation of columns without formulas?

[#1]

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!

[#2]

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

[#3]

JMP Pro V.17

Tables are attached.

[#4]

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?

[#5]

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.

[#6 · Accepted solution]

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 

[#7]

suggestion for JMP 19.1:
Query Builder: disable formula column 

[#8]

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 fills the columns 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?

[#9]

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.

[#10]

What was the response from JMP Support?