Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- perform calculations using selected columns

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 31, 2020 1:09 PM
(681 views)

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

A | B | C | new column with formula sqrt(sum(A^4, B^4, C^4) |

1 | 2 | 3 | ... |

4 | 5 | 6 | ... |

7 | 8 | 9 | ... |

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

3 REPLIES 3

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Article Labels

There are no labels assigned to this post.