How to SUM rows with variable column names?<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />I have a table with a variable number of columns. I want to add a column that SUM's all of the columns in a row no matter how many / few appear.
I tried this:
TotRow = Trans << Get Column Names(Numeric);
TR = N Items(TotRow);
Trans << New Column( "Total Time",
Numeric);
For Each Row(:Total Time[]=Sum(Column(TotRow[1::TR])[]));
But it only SUMS the first row and not the rest. The number of columns and rows are always variable week to week.
Re: How to SUM rows with variable column names?Jeff,
Thank you for the answer, and especially the link. Any day I learn something new is a good day.
Re: How to SUM rows with variable column names?That's the matrix transpose operator. It's a postfix operator, so it works on the argument to its left.
As Ian@JMP said, since JMP doesn't have an HSum() you have to use VSum() on the transposed matrix and then transpose it back.
-Jeff
Re: How to SUM rows with variable column names?Ian,
It is great to have the answer, it is better to understand the answer.
In this statement:
sumVec = (VSum(mat`))`;
What are the ` for? I understand the VSum statement, just not why ` is needed on either side of it.
Re: How to SUM rows with variable column names?Ian,
Thanks that worked perfectly!
Re: How to SUM rows with variable column names?If you want to sum all numeric columns, and you don't need a formula column, you could do:
NamesDefaultToHere(1);
// Make a table to work with
nr = 10;
nc = 3;
dt = NewTable("Some Continuous Data");
For(c=1, c<=nc, c++,
dt << NewColumn("Col "||Char(c), Numeric, Continuous, Formula(RandomInteger(1, 10)));
);
dt << AddRows(nr);
// Get the data into a matrix
mat = dt << GetAsMatrix;
// Get the sum of each row
sumVec = (VSum(mat`))`;
// Add the sum column
dt << NewColumn("Sum", Numeric, Continuous, Values(sumVec));
Note JSL does not have an 'HSum()' so you need to transpose and then transpose back.
