66948_en-US

liliconcom

Making and updating a new spreadsheet of rows from old spreadsheet that contain specific value

Hello JMP community,

 

I am very new to JMP and JSL, so I am having difficulty doing these two related tasks.

 

1) I would like to write a script that makes a new spreadsheet of rows from old spreadsheet that contain a specific entity. For example, I would like to make a script that creates a new data table of all the rows of males. (See attached pictures below)

 

2) I need to be able to autoupdate this data table. Assuming that full table is on a local db (like on SSMS), how can I use a JMP script to update the table of all males from a local SQL db? I've tried adding onto the autogenerated "Update db" script, but I have no idea what I am doing. Any help would be much appreciated.

 

Thank you so much JMP community.

 

 

 

Re: Making and updating a new spreadsheet of rows from old spreadsheet that contain specific value

@jmpandvba,

 

In the example you have provided : 

NewHt=dt<<SUBSET></SUBSET>

please note that NewHt is a reference to your data table. So, in your update statement, you are passing the reference to the data table you want to append. In the example script that I provided, that would "dt1" 

Re: Making and updating a new spreadsheet of rows from old spreadsheet that contain specific value

Last update to this thread,

 

After I created that new data table of the subset, how can I write a JSL script to update it if the original data set is appended? 

 

I'm assuming one uses the "Update" function in JSL given on page 177 of the JMP scripting guide:

 

NewHt=dt<<SUBSET></SUBSET>

But how can I do this with the specific subset we just made? 

Re: Making and updating a new spreadsheet of rows from old spreadsheet that contain specific value

@jmpandvba,

//Clean up 

Clear Log(); Clear Globals(); Close All(DataTables,"No Save"); 

// Open Sample Data 
dt = Open( "$SAMPLE_DATA/Big Class.jmp" ); 

// Subset based on condition 
dt << Select Where(:Sex == "M"); 

// Select some columns 
Column(dt,"age")<< Set Selected(1); 

dt << Invert Column Selection; 

dt1 = dt << Subset(Selected Rows(1),Selected Columns(1)); 

dt1 << Set Name("Boys In Class"); 

// Save as spreadsheet 
Close(dt1,Save("C:\Test.xlsx")); 

Re: Making and updating a new spreadsheet of rows from old spreadsheet that contain specific value

Thanks for your quick reply! Another quick question, if I wanted to make a subset of with all columns except for age, how would I do that? (I think I write the script line something like this, but what goes in the column parenthesis?)

 

subdt = dt << Subset(Column(..........), Output Table Name( "Male summary" ) );

Re: Making and updating a new spreadsheet of rows from old spreadsheet that contain specific value

@jmpandvba, 
    Answering a part of your question is easy, if you had data in JMP , you can easily create a new data table or spreadsheet as shown below. 

//Clean up 

Clear Log(); Clear Globals(); Close All(DataTables,"No Save"); 

// Open Sample Data 
dt = Open( "$SAMPLE_DATA/Big Class.jmp" ); 

// Subset based on condition 
dt << Select Where(:Sex == "M"); 

dt1 = dt << Subset(""); 

dt1 << Set Name("Boys In Class"); 

// Save as spreadsheet 
Close(dt1,Save("C:\Test.xlsx")); 

As for how do you autoupdate the data table, this would require a batch/scheduled execution of your JMP script. At a given time of the day, if you can have an application invoke your JMP script and run, you will be able to acheive it . You may look at C# or VBA automation samples of JMP for ideas. (Z:\Program Files\SAS\JMP\14\Samples\Automation)