nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Subsets for a datatable

ashwing

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.

Accepted answers

pmroz

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

All comments

pmroz

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

);

sagash

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

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

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

sagash

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

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

);