Choose Language Hide Translation Bar

Community Trekker

Joined:

Jun 3, 2014

## 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

Staff

Joined:

Jun 23, 2011

Solution

## 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)));

);

// Get the data into a matrix

mat = dt << GetAsMatrix;

// Get the sum of each row

sumVec = (VSum(mat`))`;

dt << NewColumn("Sum", Numeric, Continuous, Values(sumVec));

Note JSL does not have an 'HSum()' so you need to transpose and then transpose back.

5 REPLIES 5

Staff

Joined:

Jun 23, 2011

Solution

## 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)));

);

// Get the data into a matrix

mat = dt << GetAsMatrix;

// Get the sum of each row

sumVec = (VSum(mat`))`;

dt << NewColumn("Sum", Numeric, Continuous, Values(sumVec));

Note JSL does not have an 'HSum()' so you need to transpose and then transpose back.

Community Trekker

Joined:

Jun 3, 2014

## Re: How to SUM rows with variable column names?

Ian,

Thanks that worked perfectly!

Community Trekker

Joined:

Jun 3, 2014

## 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.

Community Manager

Joined:

Jun 23, 2011

## 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

Community Trekker

Joined:

Jun 3, 2014

## 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.