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!

 

 

Consumer and Market ResearchData AccessData Blending and CleanupRe: 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" )
);