turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Creating new col with col name

Topic Options

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

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

Sep 17, 2015 2:04 PM
(1222 views)

Hi,

I have five numerical columns. Need to create a new column reflecting the maximum values in each row of the five column. Instead of the returning the value I need to put the corresponding column name in each row.

Thanks

Sam

2 REPLIES

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

It can be done with a column formula but it's easier with a small JSL script. The loop in the last line identifies the column with the highest value and puts its name in the new column "max". If several columns have the same maximum value, the first instance will be picked.

dt = Current Data Table**()**;

cols = dt << **getcolumn names****(**numeric**)**;

newcol = dt << **New Column****(**"max", character**)**;

For Each Row**(**newcol**[]** = cols**[**Loc Max**(**Matrix**(**cols**))]** << **get name****)**;

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

hi Sam,

at the most basic level, try this:

dt = New Table**(** "data table",

New Column**(** "X1", Formula**(** Random Uniform**()** **)** **)**,

New Column**(** "X2", Formula**(** Random Uniform**()** **)** **)**,

New Column**(** "X3", Formula**(** Random Uniform**()** **)** **)**,

New Column**(** "X4", Formula**(** Random Uniform**()** **)** **)**,

New Column**(** "X5", Formula**(** Random Uniform**()** **)** **)**,

add rows**(** **100** **)**

**)**;

dt << **New Column(** "Maximum Col Name",

Character,

formula**(**

If**(**

:X1 == Maximum**(** :X1, :X2, :X3, :X4, :X5 **)**, "X1",

:X2 == Maximum**(** :X1, :X2, :X3, :X4, :X5 **)**, "X2",

:X3 == Maximum**(** :X1, :X2, :X3, :X4, :X5 **)**, "X3",

:X4 == Maximum**(** :X1, :X2, :X3, :X4, :X5 **)**, "X4",

:X5 == Maximum**(** :X1, :X2, :X3, :X4, :X5 **)**, "X5",

**)**

**)**

**)**;

from this you can generalize it further.

please notice that if there are equal values that are the maximum in one row this formula will prefer the column by his location in the if statement (for good and bad).