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

Question

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.

uday_guntupalli · Answer

@jmpandvba,

In the example you have provided :

NewHt=dt<<Subset(Columns(Name, Height), Output Table Name("hts"));
dt<<Update(With(NewHt),By Matching Columns(name==name),);

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"

jmpandvba · Answer

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(Columns(Name, Height), Output Table Name("hts"));
dt<<Update(With(NewHt),By Matching Columns(name==name),);

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

uday_guntupalli · Answer

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