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
- :
- How do I iterate through rows to find most common ...

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

Mar 30, 2017 1:04 PM
(2148 views)

Hi!

I have a table with multiple rows for a given identifier, and I would like to find the most common rows and max values of associated rows. The example below should help illustrate what I am trying to do:

The most common Field 2 value corresponding to the value of "CC" in Field 1 is "MG" and the maximum value from the "Value" field is 220,264,000,000. I think I need to use the "lag" function to iterate through the rows corresponding to each of the unique values in Field 1, but have not been able to get both the most common Field 2 and Max Value with the same bit of JSL code.

Any tips are much appreciated!

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

Mar 30, 2017 1:55 PM
(4207 views)

Solution

Hi Chris,

perhaps this two step approach cant help:

```
dt2 = Data Table( "Sample Data Table" ) << Summary( Group( :Field 1, :Field 2 ), Max( :Value ) );
todelete = dt2 << get rows where (:N Rows != colmax(:N Rows, :Field 1));
```

dt2 << delete rows (todelete);

it first does what Lou suggests and then cleans the non max combinations to achieve the final result.

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

Mar 31, 2017 4:07 PM
(3878 views)

Solution

Hi Ron -

Thanks! This works perfectly - appreciate the help!

-Chris

4 REPLIES

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

Mar 30, 2017 1:14 PM
(2146 views)

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

Mar 30, 2017 1:36 PM
(2134 views)

Hi Lou -

Thanks for the quick response! My goal is to have an output table with one row per unique entitry from Field 1 with corresponding most frequent Field 2 and Max value... It's essentially a data consolidation problem where I need to filter down to concise dataset with no repeated rows...

Thanks,

Chris

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

Mar 30, 2017 1:55 PM
(4208 views)

Hi Chris,

perhaps this two step approach cant help:

```
dt2 = Data Table( "Sample Data Table" ) << Summary( Group( :Field 1, :Field 2 ), Max( :Value ) );
todelete = dt2 << get rows where (:N Rows != colmax(:N Rows, :Field 1));
```

dt2 << delete rows (todelete);

it first does what Lou suggests and then cleans the non max combinations to achieve the final result.

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

Mar 31, 2017 4:07 PM
(3879 views)

Solution

Hi Ron -

Thanks! This works perfectly - appreciate the help!

-Chris