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

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

jbolinger — Sat, 10 Jun 2023 23:42:16 GMT

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!

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

jthi — Fri, 07 Jan 2022 18:09:53 GMT

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

txnelson — Fri, 07 Jan 2022 18:20:27 GMT

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

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

jbolinger — Fri, 07 Jan 2022 18:44:55 GMT

Thank you for the response, jthi! I'm gonna fiddle around with this and see if I can get it to work.

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

jbolinger — Fri, 07 Jan 2022 18:52:49 GMT

Thanks for the response, txnelson! This solution worked. :D