449797_en-US

liliconcom

JMP script to Stack from column 2-infinity from a weekly updating Excel table

I have an Excel table that I am manually updating on a weekly basis. Next, I open this table in JMP and Stack every column from column 2 onward. With every weekly update, I add more columns to the initial table. I recently took an Intro to JMP Scripting class and I'm looking to automate this process a bit instead of re-Stacking it from scratch every time.

 

Here is what the table looks like in JMP before performing the Stack:

 

 

I know how to import the Excel table into JMP via a script like so:

Open(
 "Drive:\Folder\File.xlsx",
 Worksheets( "Data" ),  // etc.

 

But where I had a question is how do I change the JMP script to ask it to Stack columns 2 through end?

 

I have the following from doing a manual Stack in JMP and looking at the source script:

 

Data Table( "Data" ) << Stack(
 columns(
  :Name( "8/23/2021" ),
  :Name( "08/30/2021" ),
  :Name( "09/06/2021" ),
  :Name( "09/13/2021" ),
  :Name( "09/20/2021" )  // etc.
 ),
 Source Label Column( "Failure Group Name" ),
 Stacked Data Column( "% Share, Week of" )
);

and I have the framework of a For( function I can use:

 

For( c = 2, i <= N Col(), c++,
     //body//

but I just don't quite know how to put them together. Is it putting the Stack( command inside the For( command somehow?

 

Thanks to anyone who can help!

 

 

Data TableScriptingRe: JMP script to Stack from column 2-infinity from a weekly updating Excel table

I think that with Stack platform you can just use list of columns in the columns() argument

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Semiconductor Capability.jmp");

cols_to_stack = {"NPN1", "PNP1", "PNP2"};

dt_stacked = dt << Stack(
 columns(cols_to_stack),
 Source Label Column("Label"),
 Stacked Data Column("Data"),
 Drop All Other Columns(1)
);

So get a list of column names you have from the datatable you create from excel file by using << Get Column Names(String), then drop first column with Remove From()

Names Default To Here(1);

dt = Open(
 "$SAMPLE_IMPORT_DATA/Team Results.xlsx",
 Worksheets("Ungrouped Team Results"),
 Worksheet Settings(Headers Start on Row(3), Data Starts on Row(4))
);
//all columns
col_names = dt << Get Column Names("String");

//drop first column
Remove From(col_names, 1);

//stack with remaining, won't work exactly like this with this example data, as data types aren't same
//dt_stacked = dt << Stack(...)

See Help / Scripting Index and search for different functions used here.

Re: JMP script to Stack from column 2-infinity from a weekly updating Excel table

Here is one way to handle this.   BTW, your example data table could not be opened, so I have added one of my own.

names default to here(1);
dt=data table("Example");

colNames = dt << get column names();

remove from(colNames, 1, 2 );

dt << Stack(
 columns( colNames
 ),
 Source Label Column( "Failure Group Name" ),
 Stacked Data Column( "% Share, Week of" )
);