BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
xenon2090
Community Trekker

Copy 100+ column headers with formulas from a JMP file for JSL scripting

Hello,

For volume data analysis, I was importing excel or CSV large raw data files to JMP and afterward, I was doing copy/paste of 100+ column headers with formulas from one of my prior files to each JMP file manually. I found this method very time consuming and I want to migrate to JSL scripring.

 

Is there any simple way that I save these 100+ column headers with their formulas from one of my files in a single file (e.g. excel, txt or config) and afterward, for each data analysis, I simply use a JSL script to create these new columns with formuals automatically?

 

 

0 Kudos
6 REPLIES 6
txnelson
Super User

Re: Copy 100+ column headers with formulas from a JMP file for JSL scripting

JMP 14 has a Formula Depot that will let you store your formulas, and then apply them to any data table you want to apply them to.

Also, you can easily capture the scripts for all of your formula columns, and save them to a script, and then for future data tables, you can just run the script and it will generate the new columns.

 

Here is a simple script that will generate a new Formula Depot....assuming you have JMP 14

Formula Depot(
	Predictor(
		Table( "Untitled 179" ),
		Creator( "Column Ratio" ),
		Command( "Column<<Get Script" ),
		Formula(
			New Column( "Ratio",
				Numeric,
				"Continuous",
				Format( "Best", 12 ),
				Formula( :height / :weight ),
				Set Selected
			)
		),
		Inputs( {"height"(1), "weight"(1)} )
	),
	Predictor(
		Table( "Untitled 179" ),
		Creator( "Column Reverse Ratio" ),
		Command( "Column<<Get Script" ),
		Formula(
			New Column( "Reverse Ratio",
				Numeric,
				"Continuous",
				Format( "Best", 12 ),
				Formula( :weight / :height ),
				Set Selected
			)
		),
		Inputs( {"height"(1), "weight"(1)} )
	)
);

And here is an alternate method, using a simple script that when run will generate 2 new columns, and apply their formulas

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "Ratio",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( :height / :weight ),
	Set Selected
);
dt << New Column( "Reverse Ratio",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( :weight / :height ),
	Set Selected
);

If you open up the sample JMP data table "Big Class", you can try applying the new columns from both the Formula Depot and/or the simple script

 

Jim
xenon2090
Community Trekker

Re: Copy 100+ column headers with formulas from a JMP file for JSL scripting

I have JMP 12. Is there a solution?

0 Kudos
txnelson
Super User

Re: Copy 100+ column headers with formulas from a JMP file for JSL scripting

Only the Formula Depot script requires JMP 14.  The second script will work for JMP 12.  Just open the "Big Class" data table and then run the script, and you will see that it applies the columns.

Jim
ms
Super User ms
Super User

Re: Copy 100+ column headers with formulas from a JMP file for JSL scripting

I think this approach would work in JMP 12.

 

JSL example of tranfering all formula columns from an old/template table to a new table without the formula columns:

Names Default To Here(1);

// Old table with all desired formula columns
dt = Open("$SAMPLE_DATA/Diabetes.jmp");

// New table without the formula columns
dt_new = dt << subset(Columns({1, 4 :: N Col(dt)}));

//Get scripts for all formulas columns in old table
formula_columns = {};
For(i = 1, i <= N Col(dt), i++,
    If(!Is Empty(Column(dt, i) << get property(Formula)),
        Insert Into(formula_columns, Column(dt, i))
    )
);

//Add formula columns to new table
Current Data Table(dt_new);
Eval List(formula_columns << get script);

Re: Copy 100+ column headers with formulas from a JMP file for JSL scripting

Though it's not a script, you may want to use this approach as it actually will have just two to four steps, depending on how you count :)

Assumption: You have a JMP Table with those 100+ Columns and Formulas but no data (reference table)

1. Import your data as usual and open the reference table

2. Copying the column names

a) In the column overview on the left, mark all columns, copy (ctrl+c)

b) In the new table mark all columns in the column overview, paste (ctrl+v)

3. Copying the formulas

a) mark all columns in the reference table, right click in the selected area of the table and select "Copy Multiple Column Properties"

b) mark all columns in the new table, right click in the selected area of the table and select "Paste Multiple Column Properties"

 

This should do what you need. 

 

Note: In JMP 12 ther eis no script for copy multiple column properties, in later versions (at least JMP 14 there is):

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Tiretread.jmp" );
dt << Select Columns( :MODULUS, :ELONG );
dt << Copy Multi Columns Properties;

 

julian
Staff

Re: Copy 100+ column headers with formulas from a JMP file for JSL scripting

Hi @xenon2090,

 

I wrote an Add-In that might be useful for you in this situation, Copy Script for Selected Columns. Once installed you can select your formula columns, run the Add-In from the Add-Ins menu, and you will have the script to recreate those exact columns as jsl in your clipboard. Paste that script to a new window, and run anytime you want to add those columns to the current table.

 

Julian