topic Re: How to make a general / variable script for splitting a data table and performing calculations? in Discussions

How to make a general / variable script for splitting a data table and performing calculations?

kh — Tue, 03 Apr 2018 15:46:17 GMT

I routinely perform the same data table manipulation and calculations and am interested in automating that with a script.

The general steps are something like:

Split table (split column "value" by column "attribute") > generates a new table
Generate a new column (C) = column A + column B
Generate new column (D) = column A / column C

However, every table has a different number of rows and different values. When I click through these functions and examine the script generated by JMP, the number of rows and values in each column are hard-coded. How do I make these completely variable so I can run the script on new data tables of any size?

So far, I have a script that looks like this, but it prompts you for the number of rows, then just generates an empty table. I'm having a hard time finding documentation, so any help is appreciated!

New Table( "split table",
	Add Rows(),
	New Script(
		"Source",
		Data Table() <<
		Split(
			Split By( :attribute ),
			Split( :value ),
			Output Table( "split table" ),
			Sort by Column Property	) ),
	New Column( "date", Character( 1 ), "Nominal", Set Values( {""} ) ),
	New Column( "A", Numeric, "Continuous", Format( "Best", 12 ), Set Values( {""} ) ),
	New Column( "B", Numeric, "Continuous", Format( "Best", 12 ), Set Values( {""} ) ),
	New Column( "C", Numeric, "Continuous", Format( "Best", 12 ), Formula( :Name( "A" ) + :Name( "B" ) ) ),
	New Column( "D", Numeric, "Continuous",	Format( "Percent", 12, 0 ), Formula( :Name( "A" ) / :Name( "C" ) ) ) )

Re: How to make a general / variable script for splitting a data table and performing calculations?

txnelson — Tue, 03 Apr 2018 14:43:25 GMT

1. Your attached data table is corrupted. I opened it using a text editor, and it has messages of having deleted the data.

2. From the text of your question, I think I understand how you are thinking your script structure should be doing what you want, but in fact, it will just generate an empty data table....as you have reported it does.

3. Here is what I think you want as the starting for your script

Names Default To Here( 1 );

// Create a pointer variable to be able to reference 
// the current active data table
dt = Current Data Table();

// Creat the split data table
dt2 = dt << Split(
	Split By( :attribute ),
	Split( :value ),
	Output Table( "split table" ),
	Sort by Column Property
);

// Add the new columns to the split data table
dt2 << New Column( "date", Character( 1 ), "Nominal", Set Values( {""} ) );
dt2 << New Column( "A", Numeric, "Continuous", Format( "Best", 12 ), Set Values( {""} ) );
dt2 << New Column( "B", Numeric, "Continuous", Format( "Best", 12 ), Set Values( {""} ) );
dt2 << New Column( "C",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( :Name( "A" ) + :Name( "B" ) )
);
dt2 << New Column( "D",
	Numeric,
	"Continuous",
	Format( "Percent", 12, 0 ),
	Formula( :Name( "A" ) / :Name( "C" ) )
);

Re: How to make a general / variable script for splitting a data table and performing calculations?

kh — Tue, 03 Apr 2018 15:52:07 GMT

Hi Jim,

Sorry about the data table. It seems to work fine on my computer, so I've simply reuploaded it.

I tried your script and it works except for one thing: creating the columns "date", "A" and "B" are redundant, because those columns get carried over to the new table by the split table function. So the script becomes even simpler and works perfectly:

Names Default To Here( 1 );

// Create a pointer variable to be able to 
// reference the current active data table
dt = Current Data Table();

// Create the split data table
dt2 = dt << Split(
	Split By( :attribute ),
	Split( :value ),
	Output Table( "split table" ),
	Sort by Column Property
);

// Add new columns to the split data table
dt2 << New Column( "C",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( :Name( "A" ) + :Name( "B" ) )
);
dt2 << New Column( "D",
	Numeric,
	"Continuous",
	Format( "Percent", 12, 0 ),
	Formula( :Name( "A" ) / :Name( "C" ) )
);

Thank you so much!