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
- :
- Return the Row Number of Maximum

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

Highlighted

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

Jan 11, 2018 9:54 AM
(1630 views)

Hi,

Is it possible to get the row number of the maximum value of a column?

I would like to create a formula that uses a value in a column as an offset and I would like to write it in generic form such that it looks in Col1 for a maximum value and then returns the row number such I can use the value of col2 at that row number as the offset.

How could I do this?

Thanks in advance

Andy

1 ACCEPTED SOLUTION

Accepted Solutions

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

Here is a simple solution to your issue. It can be moved directly into a formula, or used in open code as it is shown:

```
names default to here(1);
dt=open("$SAMPLE_DATA\Semiconductor Capability.jmp" );
MaxRow=(dt<<get rows where(Col Maximum(:NPN1)==:NPN1))[1];
show(MaxRow);
```

Jim

6 REPLIES

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

Here is a simple solution to your issue. It can be moved directly into a formula, or used in open code as it is shown:

```
names default to here(1);
dt=open("$SAMPLE_DATA\Semiconductor Capability.jmp" );
MaxRow=(dt<<get rows where(Col Maximum(:NPN1)==:NPN1))[1];
show(MaxRow);
```

Jim

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

Beat me to it.

-- Cameron Willden

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

Col Max can return the max value in a column. Here's how you can find the row and use it to return the corresponding value in a different column using the Big Class sample data set:

dt = Current Data Table(); max_row = dt << Get Rows Where(:height == Col Max(:height)); dt:weight[max_row]

-- Cameron Willden

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

Can I do this only using the formula editor?

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

@Hegedus wrote:Can I do this only using the formula editor?

Sure. Find the row with max value for column X1 and return the corresponding value for X2:

:X2[Current Data Table() << Get Rows Where( Col Maximum( :X1 ) == :X1 )]

You can copy that right into the formula editor and it will work.

-- Cameron Willden

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

Thank you!

Slightly tweaked but this is what I have and it seems to work.