cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Discussions

Solve problems, and share tips and tricks with other JMP users.
Choose Language Hide Translation Bar
dyakunin1
Level I

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:

dyakunin1_0-1760063081445.png

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:

dyakunin1_1-1760063388612.png

":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!

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

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).

jthi_0-1760074384015.png

 

Also, I would contact JMP support about this as there is some weirdness going on, JMP Support 

-Jarmo

View solution in original post

12 REPLIES 12
hogi
Level XIII

Re: 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

hogi_2-1760069921288.png

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?]

 

hogi_1-1760069665334.png

 

 

hogi_0-1760069558813.png

 

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

 

dyakunin1
Level I

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

JMP Pro V.17

Tables are attached.

 

jthi
Super User

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?

-Jarmo
dyakunin1
Level I

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.

jthi
Super User

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).

jthi_0-1760074384015.png

 

Also, I would contact JMP support about this as there is some weirdness going on, JMP Support 

-Jarmo
hogi
Level XIII

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

suggestion for JMP 19.1:
Query Builder: disable formula column 

hogi
Level XIII

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 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?

 

 

dyakunin1
Level I

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.

hogi
Level XIII

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

What was the response from JMP Support?

Recommended Articles