cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
bjbreitling
Level IV

Exclude data when loading data with a script

Hi community,

When I open a table with a database with the following script saved to the datatable it works.  When I save the script to a menu item in an addin it just pulls the data and doesn't exclude.  Whats up?

Open Database(

  "DSN=PI SQL Connection Lab;Description=PI SQL Connection Lab;UID=;Trusted_Connection=Yes;APP=JMP;WSID=SFX-5GCN2M1;DATABASE=LabData",

  "SELECT * FROM dbo.NSI_Discovered_Factors_Lab_Data",

  "NSI_Discovered_Factors_Lab_Data",

"NSI Database"

);

dt=CurrentDataTable();

dt<<Select Where(Exclude=="Exclude")<<hide<<Exclude;

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: Exclude data when loading data with a script

Thanks for the clue about it working when run from a script editor but not as an add-in.

That usually indicates a scoping problem for variable references in your JSL. By default, add-ins run with the "Names Default to Here(1)" option, so that un-scoped references go to the "Here" namespace.

Your Select Where() has an un-scoped variable in it in the form of Exclude.

If you include a : before it to scope it to a data table column it works in both cases (script editor and add-in).


dt<<Select Where(:Exclude=="Exclude")<<hide<<Exclude;


-Jeff

-Jeff

View solution in original post

9 REPLIES 9
Byron_JMP
Staff

Re: Exclude data when loading data with a script

Its possible that the Open isn't complete when the Select is executed?

I haven't seen this exact problem, but something like it when I'm working with tables that have lots of rows.

Two suggestions:

First, assign the data table to a variable when its opened. This way dt=current data table(); doesn't accidentally end up pointing at the current table, while the database table is opening.

     dt=open database("DNS....

     Leave out the dt=current data table();

Second, force dt to be evaluated before selecting rows. It can't be evaluated before it finishes opening

     dt<<Run Formulas; //include this first

     dt<<Select Where(Exclude=="Exclude")<<hide<<Exclude;

JMP Systems Engineer, Health and Life Sciences (Pharma)
bjbreitling
Level IV

Re: Exclude data when loading data with a script

Hi Byron,

That didn't fix it.  I also made a separate addin that should work given what you said is true which I execute after the loading of the dataset which is just the following.

dt=CurrentDataTable();

dt<<Select Where(Exclude=="Exclude")<<hide<<Exclude;

This oddly doesn't work either.

pmroz
Super User

Re: Exclude data when loading data with a script

Try using wait(0) instead of run formulas.  I agree with Byron - remove the dt = current data table and set dt = to the results of open database.  Like this:

dt = Open Database(

  "DSN=PI SQL Connection Lab;Description=PI SQL Connection Lab;UID=;Trusted_Connection=Yes;APP=JMP;WSID=SFX-5GCN2M1;DATABASE=LabData",

  "SELECT * FROM dbo.NSI_Discovered_Factors_Lab_Data",

  "NSI_Discovered_Factors_Lab_Data",

  "NSI Database");

wait(0);

dt << Select Where(Exclude=="Exclude") << hide << Exclude;

bjbreitling
Level IV

Re: Exclude data when loading data with a script

Same issue PMroz.  Oddly it still doesn't work.

pmroz
Super User

Re: Exclude data when loading data with a script

Here's a thought.  Since you're pulling the data with SQL, why don't you exclude the rows in SQL?  Also I think you have an extra parameter to Open Database (NSI Database).

dt = Open Database(

  "DSN=PI SQL Connection Lab;Description=PI SQL Connection Lab;UID=;Trusted_Connection=Yes;APP=JMP;WSID=SFX-5GCN2M1;DATABASE=LabData",

  "SELECT * FROM dbo.NSI_Discovered_Factors_Lab_Data WHERE exclude != 'Exclude'",

  "NSI_Discovered_Factors_Lab_Data",

  "NSI Database");

bjbreitling
Level IV

Re: Exclude data when loading data with a script

That is an option PMroz, but I would prefer pulling the all the data and leave folks the option to un-exclude.

Byron_JMP
Staff

Re: Exclude data when loading data with a script

There may be a couple of other little things to check, and I apologize in advance for being so basic. 

The imported data table has a column named "Exclude"

-does the actual column name have any leading spaces?

-does the actual column name match? e.g. title case and spelling.

The values to filter on in the column named "Exclude"

-do the values in the column have any leading or trailing spaces?

-is the spelling and title case exact for the level "Exclude"?

The script in the data table works, did it come from the Data Filter?

JMP Systems Engineer, Health and Life Sciences (Pharma)
bjbreitling
Level IV

Re: Exclude data when loading data with a script

Hi Byron,

When I copy and paste the query into a script which is exactly the following:

dt=CurrentDataTable();

dt<<Select Where(Exclude=="Exclude")<<hide<<Exclude;

It works.  But when I save the script to a jsl addin, it oddly doesn't.  The column name and case and spaces match.  So does it for the rows.  Its very odd that it works in non-addin jsl but not in addin.  I didn't generate this jsl form the data filter.

Jeff_Perkinson
Community Manager Community Manager

Re: Exclude data when loading data with a script

Thanks for the clue about it working when run from a script editor but not as an add-in.

That usually indicates a scoping problem for variable references in your JSL. By default, add-ins run with the "Names Default to Here(1)" option, so that un-scoped references go to the "Here" namespace.

Your Select Where() has an un-scoped variable in it in the form of Exclude.

If you include a : before it to scope it to a data table column it works in both cases (script editor and add-in).


dt<<Select Where(:Exclude=="Exclude")<<hide<<Exclude;


-Jeff

-Jeff