Subscribe Bookmark RSS Feed

Subsets for a datatable

ashwing

Community Trekker

Joined:

Apr 12, 2012

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
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
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

sagash

Community Trekker

Joined:

Apr 18, 2012

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

ms

Super User

Joined:

Jun 23, 2011

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

sagash

Community Trekker

Joined:

Apr 18, 2012

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

exj

Community Trekker

Joined:

Apr 18, 2012

Remember to mark the answer solved after good advices and mark the right answer and helpful answers also (=makes the usage of the forum more efficient)!

sagash

Community Trekker

Joined:

Apr 18, 2012

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,

pmroz

Super User

Joined:

Jun 23, 2011

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

);