perform calculations using selected columns

Question

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!

txnelson · Accepted Answer

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

txnelson · Accepted Answer

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()]