turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Subsets for a datatable

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 12, 2012 10:18 PM
(1631 views)

Hi All,

I'm pretty novice to JMP coding and need your help in figuring this out.

Basically what I'm trying to do is import an excel file and save it as a datatable and that I'm achieving by using this command.

**Open Database("DSN=Excel Files;DBQ="xyz".xlsm;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", "SELECT * FROM `'CPASU by queue$'`","CPASU by queue$");**

so far so good.

and then from the CPASU by queue data table that's been created - I'm trying to create a subset of that data table by using the following command

**(Data Table( "CPASU by queue$" ) << SELECT WHERE AND(FWEEK > 201201,Forecast accuracy > 0)**

** <<Subset(**

** Linked,**

** Output Table Name( "Subset of Forecast Accuracy$" )));**

**Now when I execute these commands I'm perfectly able to create the data table CPASU by queue and also the subset table Subset of Forecast Accuracy. However rows returned in subset table are not returned as per the condition I have given in the select clause. It is returning all rows from the main data table ( CPASU by queue)... **

Can someone please help me out in figuring this one?

Also if there is a book which I can follow to understand JMP JSL....I've tried to follow the script thats automatically created by the JMP GUI but its been a nightmare so far.

Thank you in advance for all the help.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Solution

You should only make a subset if any rows were found for your search. If no rows were found the subset will just be the same as your original table. You need something like this. I've included some code improvements for readability.

dt = Open Database**(**"DSN=Excel Files;DBQ="xyz".xlsm;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;",

"SELECT * FROM `'CPASU by queue$'`","CPASU by queue$"**)**;

// Select rows as per your condition

dt << select where **(**:FWEEK > **201201** & :Forecast accuracy > **0)**;

// See if any rows were selected

found_rows = nrows**(** dt << Get Selected Rows **)**;

if **(**found_rows > **0**,

// then

dt << subset**(**output table name**(** "Subset of Forecast Accuracy$" **))**,

// else

print**(**"No rows were selected"**)**

**)**;

There are several excellent JMP scripting books out there. There's the JMP Script PDF manual included with JMP, but there is also the book **Jump into JMP(r) Scripting **by Wendy Murphrey and Rosemary Lucas, ISBN 978-1-59994-658-0

7 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You should only make a subset if any rows were found for your search. If no rows were found the subset will just be the same as your original table. You need something like this. I've included some code improvements for readability.

dt = Open Database**(**"DSN=Excel Files;DBQ="xyz".xlsm;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;",

"SELECT * FROM `'CPASU by queue$'`","CPASU by queue$"**)**;

// Select rows as per your condition

dt << select where **(**:FWEEK > **201201** & :Forecast accuracy > **0)**;

// See if any rows were selected

found_rows = nrows**(** dt << Get Selected Rows **)**;

if **(**found_rows > **0**,

// then

dt << subset**(**output table name**(** "Subset of Forecast Accuracy$" **))**,

// else

print**(**"No rows were selected"**)**

**)**;

There are several excellent JMP scripting books out there. There's the JMP Script PDF manual included with JMP, but there is also the book **Jump into JMP(r) Scripting **by Wendy Murphrey and Rosemary Lucas, ISBN 978-1-59994-658-0

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 18, 2012 12:26 AM
(714 views)

I tried doing that but it still doesn't return any new result. Below is the full code.

// - Opening the file and establishing a connection in JMP.

Open Database("DSN=Excel Files;DBQ=C:\Users\\Documents\\JMP Analysis\XXX.xlsm;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", "SELECT * FROM `'CPASU by queue$'`","CPASU by queue$");

//Create the 1st subset

(Data Table( "CPASU by queue$" ) << Select Where(AND(FWEEK>=201201,FWEEK!=201248, FWEEK!=201247, FWEEK!=201243, Actual CPASU>0))<< Subset( Linked ) ;

Current Data Table( Data Table( "Subset of CPASU by queue$" ) ));

//Creates the 2nd subset for forecast accuracy

dp = (Data Table( "CPASU by queue$" ) << SELECT WHERE AND(FWEEK > 201201,No Masking != 0));

found_rows = nrows( dp << Get Selected Rows );

if (found_rows > 0,

// then

dp << subset(output table name( "Subset of Forecast Accuracy$" )),

// else

write("No rows were selected")

);

The problem is in the second subset. Although it is creating the second subset it is returning the same result set as the first subset's query and I'm pretty sure in the Data table CPASU by queue there are several rows that match the sql condition of SELECT WHERE AND(FWEEK > 201201,No Masking != 0));

Please help....Thank you

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 18, 2012 12:55 AM
(714 views)

It looks like you have misplaced some parenthesis in the second subset selection. You must put the And() inside Select Where().

Like this:

dp = Data Table**(** "CPASU by queue$" **)** << SELECT WHERE**(** And**(** FWEEK > **201201**, No Masking != **0** **)** **)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 18, 2012 8:16 AM
(714 views)

yes I think the problem was in the placing of parenthesis...It worked now...Thank you..

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 20, 2012 1:12 AM
(714 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 30, 2012 2:14 PM
(714 views)

Ok the problem has surfaced again...Not really sure what happened to the code

dp = Data Table**(**"CPASU by queue$"**)**<< SELECT WHERE**(** AND**(**FWEEK > **201201**,No masked != **0))**;

found_rows = nrows**(** dp << Get Selected Rows **)**;

if**(**found_rows>**0)**

dp << subset**(**output table name**(** "Subset of Forecast Accuracy$" **))**;

CPASU by queue is a datatable created off from an excel file earlier in the code. Please help.

Thanks,

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 30, 2012 5:58 PM
(714 views)

Your IF statement closed the parentheses too soon and you were missing a comma:

if **(**found_rows > **0,**

dp << subset**(**output table name**(** "Subset of Forecast Accuracy$" **))**;

);