topic Re: Exclude data when loading data with a script in Discussions

Exclude data when loading data with a script

bjbreitling — Tue, 05 Aug 2014 21:54:59 GMT

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;

Re: Exclude data when loading data with a script

Byron_JMP — Wed, 06 Aug 2014 14:51:34 GMT

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;

Re: Exclude data when loading data with a script

bjbreitling — Wed, 06 Aug 2014 15:12:04 GMT

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.

Re: Exclude data when loading data with a script

pmroz — Wed, 06 Aug 2014 15:48:33 GMT

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;

Re: Exclude data when loading data with a script

bjbreitling — Thu, 07 Aug 2014 14:29:30 GMT

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

Re: Exclude data when loading data with a script

pmroz — Thu, 07 Aug 2014 14:59:12 GMT

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

Re: Exclude data when loading data with a script

bjbreitling — Thu, 07 Aug 2014 18:36:22 GMT

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

Re: Exclude data when loading data with a script

Byron_JMP — Thu, 07 Aug 2014 19:08:22 GMT

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?

Re: Exclude data when loading data with a script

bjbreitling — Thu, 07 Aug 2014 19:22:48 GMT

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.

Re: Exclude data when loading data with a script

Jeff_Perkinson — Thu, 07 Aug 2014 19:44:11 GMT

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