This is a question about JMP Live Data Table Refresh scripts. Specifically, those that are source scripts from using Query Builder to connect to a database in JMP with Post Query Scripts. @LuizaFabreti and I have been playing around with a few examples and it is not clear what gets executed in the PostQueryScript in JMP Live and what does not (what gets ignored). The PostQueryScript in both cases is adding a new formula column that concatenates two columns (Drug ID and Description) to create a new column. * https://jmplive.jmp.com/packages/ln5fhkz5vmLjRQr8_bP8b/settings - The PostQueryScript here works; the JMP Live table reflects the new column with a formula. * https://jmplive.jmp.com/packages/Px8K7w9SjBpLNcNQL7Vmy/settings - This version does not work. I think because I use New Formula Column() * https://jmplive.jmp.com/packages/TVVS_6jvz7GYd9kz82Qnm/settings - This one kind of works. It creates the new formula column by using New Column(). But the second part of the JSL which moves the column next to Description does not work. Has anyone seen this before? Guidance? @dieterpisot @Alex_Lippincott

I have attached the .jmpquery files that go along with the following. They both work in JMP. We have a Discovery paper for Baltimore where I am going to make Alex's suggestion: move things outside the Post-Query script before posting to JMP Live OR start from scratch when building the data refresh script (versus relying on Source Script). * https://jmplive.jmp.com/packages/Px8K7w9SjBpLNcNQL7Vmy/settings - This version does not work. I think because I use New Formula Column() * https://jmplive.jmp.com/packages/TVVS_6jvz7GYd9kz82Qnm/settings - This one kind of works. It creates the new formula column by using New Column(). But the second part of the JSL which moves the column next to Description does not work. Stability_Data_Stacked New Formula Column.jmpquery

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JMP Live Data Table Refresh Script (from Query Builder)

wendytseng

This is a question about JMP Live Data Table Refresh scripts. Specifically, those that are source scripts from using Query Builder to connect to a database in JMP with Post Query Scripts.

@LuizaFabreti and I have been playing around with a few examples and it is not clear what gets executed in the PostQueryScript in JMP Live and what does not (what gets ignored).

The PostQueryScript in both cases is adding a new formula column that concatenates two columns (Drug ID and Description) to create a new column.

https://jmplive.jmp.com/packages/ln5fhkz5vmLjRQr8_bP8b/settings - The PostQueryScript here works; the JMP Live table reflects the new column with a formula.
https://jmplive.jmp.com/packages/Px8K7w9SjBpLNcNQL7Vmy/settings - This version does not work. I think because I use New Formula Column()
https://jmplive.jmp.com/packages/TVVS_6jvz7GYd9kz82Qnm/settings - This one kind of works. It creates the new formula column by using New Column(). But the second part of the JSL which moves the column next to Description does not work.

Has anyone seen this before? Guidance?

@dieterpisot

@Alex_Lippincott

Accepted answers

All comments

dieterpisot

To my knowledge JMP Live doesn't treat the PostQueryScript any different than JMP does. Have you tried running the refresh script in JMP? Does it work there?

dieterpisot

Also the second and third script are runnign into errors like this one:

Name Unresolved: New Formula Column in access or evaluation of 'New Formula Column' , New Formula Column(
	Operation( Category( "Character" ), "Concatenate with Space" ),
	Columns( :Drug ID, :Description )
) /*###*/

at line 1
Data Table( "Stability_Data_Stacked 2" )

Alex_Lippincott

My experience is similar. Sometimes it works and sometimes it does not. The New Column() works but anything fancier I recommend doing outside the query in JSL. I'm not sure what is supposed to be supported but I find post query scripts not that great.

martindemel

haven't tested anything, but sometimes you might check if the columns you create and want to use later are actually already created before the interpreter get through the rest of the script. This is most often the issue when referncing to table names, column names created, and other things like search and select rows to be acted on, ...
a wait() or wait(2) might help to check if this is the reason for the error. If yes, you might want to change the code structure, keep wait() command in the script if performance is not dropping, think about alternative approaches, ...
Table manipulations in a postquery script are kinda difficult, you may want to do data table management in the refresh script as soon the table has been created, and not in the fetch itself.

wendytseng

I have attached the .jmpquery files that go along with the following. They both work in JMP.

We have a Discovery paper for Baltimore where I am going to make Alex's suggestion: move things outside the Post-Query script before posting to JMP Live OR start from scratch when building the data refresh script (versus relying on Source Script).

https://jmplive.jmp.com/packages/Px8K7w9SjBpLNcNQL7Vmy/settings - This version does not work. I think because I use New Formula Column()
https://jmplive.jmp.com/packages/TVVS_6jvz7GYd9kz82Qnm/settings - This one kind of works. It creates the new formula column by using New Column(). But the second part of the JSL which moves the column next to Description does not work.

Stability_Data_Stacked New Formula Column.jmpquery

wendytseng

Having issues posting the second .jmpquery file but happy to email it to you if you want to try it out.

The only difference is the post-query script (in JMP Live, the column is created but is not moved):

// New column: Drug ID, Description

Data Table( "Stability_Data_Stacked" ) << New Column( "Drug ID, Description",

Character,

"Nominal",

Formula( Char( :Drug ID ) || " " || :Description )

) << Move Selected Columns( {:"Drug ID, Description"n}, after( :Description ) ) <<

Run Formulas;

dieterpisot

I think Martin's guess is a pretty good one. It's some sort of timing issue. It could be that PostQueryScript runs asynchronously and the SQL query returns to the script before the post query is done. The JMP Live JMP session doesn't know it has to wait and returns the table before the post query finishes. Adding a wait() to allow the post query to finish is probably a good workaround.