cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
KellyT
Level II

perform calculations using selected columns

Hi,

I am struggling to find a good scripting solution for this.  I would like to select a few columns and then perform calculations across them.  The selected columns are flexible, sometimes it could be 2 but sometimes it could be 10+ columns to perform this calculation.

 

For example 

ABCnew column with formula sqrt(sum(A^4, B^4, C^4)
123...
456...
789...

 

At this moment, I have the script to extract selected column. But once they are in a list with column name, I don't know how to sum up the column using the formula I have above...

dt = Current Data Table();
cols = dt << get selected columns;

Thank you so much for your help!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: perform calculations using selected columns

Here is, using JSL,

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

dt << New Column( "Calc",
	formula(
		cols = dt << get selected columns;
		mat = [];

		For( i = 1, i <= N Items( cols ), i++,
			mat = mat || Column( cols[i] )[Row()]
		);
		mat = Sqrt( Sum( mat ^ 4 ) );
	)
);

dt:calc << delete formula;

how I would create a formula column to handle your issue

 

Jim

View solution in original post

txnelson
Super User

Re: perform calculations using selected columns

I chose the method to use, based upon the very nice capabilities the Matrix operators have in JSL.  The statement

mat = Sqrt( Sum( mat ^ 4 ) );

is an illustration of how nice and concise the calculations can be made using matrices. 

But to use matrix operators, the data must be in a matrix. So for each row, the matrix Mat is first set to being an empty matrix

mat = [];

and then the For() loop takes each of the columns specified in the << Get Selected Columns and adds the value for that column for the given row, to the matrix "Mat" as a new element in the matrix.  It is "Concatenating" the value of the column for the current row to the matrix.

mat = mat || Column( cols[i] )[Row()]
Jim

View solution in original post

3 REPLIES 3
txnelson
Super User

Re: perform calculations using selected columns

Here is, using JSL,

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

dt << New Column( "Calc",
	formula(
		cols = dt << get selected columns;
		mat = [];

		For( i = 1, i <= N Items( cols ), i++,
			mat = mat || Column( cols[i] )[Row()]
		);
		mat = Sqrt( Sum( mat ^ 4 ) );
	)
);

dt:calc << delete formula;

how I would create a formula column to handle your issue

 

Jim
KellyT
Level II

Re: perform calculations using selected columns

Thank you Jim! Your script is very helpful as always.  To help me understand JSL better, can you please elaborate what this line of code does?

 

mat = mat || Column( cols[i] )[Row()]
txnelson
Super User

Re: perform calculations using selected columns

I chose the method to use, based upon the very nice capabilities the Matrix operators have in JSL.  The statement

mat = Sqrt( Sum( mat ^ 4 ) );

is an illustration of how nice and concise the calculations can be made using matrices. 

But to use matrix operators, the data must be in a matrix. So for each row, the matrix Mat is first set to being an empty matrix

mat = [];

and then the For() loop takes each of the columns specified in the << Get Selected Columns and adds the value for that column for the given row, to the matrix "Mat" as a new element in the matrix.  It is "Concatenating" the value of the column for the current row to the matrix.

mat = mat || Column( cols[i] )[Row()]
Jim