BookmarkSubscribeSubscribe to RSS Feed

Exclude data when loading data with a script

bjbreitling

Community Trekker

Joined:

Dec 13, 2012

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

Joined:

Jun 23, 2011

Solution

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
9 REPLIES
Byron_JMP

Staff

Joined:

Apr 26, 2012

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, Pharm and BioPharm Sciences
bjbreitling

Community Trekker

Joined:

Dec 13, 2012

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

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Dec 13, 2012

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

pmroz

Super User

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Dec 13, 2012

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

Joined:

Apr 26, 2012

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, Pharm and BioPharm Sciences
bjbreitling

Community Trekker

Joined:

Dec 13, 2012

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

Joined:

Jun 23, 2011

Solution

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