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
rleeper
Level III

How to SUM rows with variable column names?

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ian_jmp
Staff

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.


View solution in original post

5 REPLIES 5
ian_jmp
Staff

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.


rleeper
Level III

Re: How to SUM rows with variable column names?

Ian,

Thanks that worked perfectly!

rleeper
Level III

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.

Jeff_Perkinson
Community Manager Community Manager

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

-Jeff
rleeper
Level III

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.