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
- :
- Finding the max value as per ID

- 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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 5, 2018 11:06 PM
(4568 views)

Hi,

I have the following table as shown. My intention is to find the max(or min) value in the COUNT1 column per each ID1 and populate them in a new table which will have similar column as original table. Can anyone suggest what is the best way to do it.

ID1 | MAIN_RAD | SUB_RAD | COUNT1 |

M101 | 0 | 0 | 1796 |

M101 | 0 | 1 | 1583 |

M101 | 0 | 2 | 1521 |

M101 | 0 | 3 | 1621 |

M101 | 1 | 0 | 1601 |

M101 | 1 | 1 | 1539 |

M101 | 1 | 2 | 1639 |

M101 | 1 | 3 | 1610 |

M105 | 0 | 0 | 1601 |

M105 | 0 | 1 | 1539 |

M105 | 0 | 2 | 1639 |

M105 | 0 | 3 | 1610 |

M105 | 1 | 0 | 1856 |

M105 | 1 | 1 | 1960 |

M105 | 1 | 2 | 1931 |

M105 | 1 | 3 | 2004 |

M110 | 0 | 0 | 1993 |

M110 | 0 | 1 | 1779 |

M110 | 0 | 2 | 1820 |

M110 | 0 | 3 | 1813 |

M110 | 1 | 0 | 1813 |

M110 | 1 | 1 | 1736 |

M110 | 1 | 2 | 1620 |

M110 | 1 | 3 | 1643 |

Thanks a lot.

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

Hi Jim,

Thanks.

Yes, I tried it before. Sorry, perhaps I didn't described it in detail previously. In this case from each ID1 and Main_Rad, I would like to find the max value in COUNT column and copy(ID1, Main_Rad, Count1) to another table. It shall be similar as below:

ID1 | MAIN_RAD | SUB_RAD | COUNT1 | |

M101 | 0 | 0 | 1796 | Max |

M101 | 0 | 1 | 1583 | |

M101 | 0 | 2 | 1521 | |

M101 | 0 | 3 | 1621 | |

M101 | 1 | 0 | 1601 | |

M101 | 1 | 1 | 1539 | |

M101 | 1 | 2 | 1639 | Max |

M101 | 1 | 3 | 1610 | |

M105 | 0 | 0 | 1601 | |

M105 | 0 | 1 | 1539 | |

M105 | 0 | 2 | 1639 | Max |

M105 | 0 | 3 | 1610 | |

M105 | 1 | 0 | 1856 | |

M105 | 1 | 1 | 1960 | |

M105 | 1 | 2 | 1931 | |

M105 | 1 | 3 | 2004 | Max |

M110 | 0 | 0 | 1993 | Max |

M110 | 0 | 1 | 1779 | |

M110 | 0 | 2 | 1820 | |

M110 | 0 | 3 | 1813 | |

M110 | 1 | 0 | 1813 | Max |

M110 | 1 | 1 | 1736 | |

M110 | 1 | 2 | 1620 | |

M110 | 1 | 3 | 1643 |

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

Here are 3 of the ways to do this.......other examples could be thought up

```
Names Default To Here( 1 );
dt = New Table( "Sample",
Add Rows( 24 ),
New Column( "ID1",
Character,
"Nominal",
Set Values(
{"M101", "M101", "M101", "M101", "M101", "M101", "M101", "M101", "M105", "M105", "M105", "M105", "M105",
"M105", "M105", "M105", "M110", "M110", "M110", "M110", "M110", "M110", "M110", "M110"}
)
),
New Column( "MAIN_RAD",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1] )
),
New Column( "SUB_RAD",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3] )
),
New Column( "COUNT1",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values(
[1796, 1583, 1521, 1621, 1601, 1539, 1639, 1610, 1601, 1539, 1639, 1610, 1856, 1960, 1931, 2004, 1993, 1779,
1820, 1813, 1813, 1736, 1620, 1643]
)
)
);
// Create the column using a formula
dt << New Column( "By Formula",
Character( 16 ),
"Nominal",
Formula(
If( Row() == 1, flag = 0 );
If( Lag( :ID1 ) != :ID1 | Lag( :MAIN_RAD ) != :MAIN_RAD,
flag = 0
);
If( flag == 0 & Col Max( :COUNT1, :ID1, :MAIN_RAD ) == :COUNT1,
Flag = 1;
"Max";
,
""
);
)
);
// Create the column using JSL
dt << New Column( "Using JSL", character );
For( i = 1, i <= N Rows( dt ), i++,
rowID1 = dt:ID1[i];
rowMain_Rad = dt:MAIN_RAD[i];
sameRows = dt << get rows where( dt:ID1 == rowID1 & dt:MAIN_RAD == rowMain_Rad );
If( Max( dt:Count1[sameRows] ) == :COUNT1[i],
dt:Using JSL[i] = "Max"
);
);
// Create the column using Platforms
dtMax = dt << Summary(
invisible,
Group( :ID1, :MAIN_RAD ),
Max( :COUNT1 ),
Freq( "None" ),
Weight( "None" ),
statistics column name format( "column" ),
Link to original data table( 0 )
);
dtMax << delete columns( "N Rows" );
dtMax << New Column( "Using Platform", character, set each value( "Max" ) );
dt << Update( With( dtMax ), Match Columns( :ID1 = :ID1, :MAIN_RAD = :MAIN_RAD, :COUNT1 = :COUNT1 ) );
```

Jim

8 REPLIES 8

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

Re: Finding the max value as per ID

The Summary Platform is designed to do this task

Tables==>Summary

Jim

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

Hi Jim,

Thanks.

Yes, I tried it before. Sorry, perhaps I didn't described it in detail previously. In this case from each ID1 and Main_Rad, I would like to find the max value in COUNT column and copy(ID1, Main_Rad, Count1) to another table. It shall be similar as below:

ID1 | MAIN_RAD | SUB_RAD | COUNT1 | |

M101 | 0 | 0 | 1796 | Max |

M101 | 0 | 1 | 1583 | |

M101 | 0 | 2 | 1521 | |

M101 | 0 | 3 | 1621 | |

M101 | 1 | 0 | 1601 | |

M101 | 1 | 1 | 1539 | |

M101 | 1 | 2 | 1639 | Max |

M101 | 1 | 3 | 1610 | |

M105 | 0 | 0 | 1601 | |

M105 | 0 | 1 | 1539 | |

M105 | 0 | 2 | 1639 | Max |

M105 | 0 | 3 | 1610 | |

M105 | 1 | 0 | 1856 | |

M105 | 1 | 1 | 1960 | |

M105 | 1 | 2 | 1931 | |

M105 | 1 | 3 | 2004 | Max |

M110 | 0 | 0 | 1993 | Max |

M110 | 0 | 1 | 1779 | |

M110 | 0 | 2 | 1820 | |

M110 | 0 | 3 | 1813 | |

M110 | 1 | 0 | 1813 | Max |

M110 | 1 | 1 | 1736 | |

M110 | 1 | 2 | 1620 | |

M110 | 1 | 3 | 1643 |

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

Re: Finding the max value as per ID

Your second example is confusing to me. I now understand that you do not want just a summary table, but want to have the original data in it's raw form, in the new data table. But what is confusing is the placement of your MAX values in the second table. If you want the MAX for each ID1 grouping, why is there a MAX value on row 1 and row 7. Both of those rows have the same value "M101" for the ID1 column. Could you please clarify?

Jim

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

Re: Finding the max value as per ID

Hi Jim,

Sorry for the confusion.

The ID1 group can be referred as SerialNo. Assuming that for M101(and M105

and M110 as well), it has 2 Main Radius(MAIN_RAD), 0 and 1. Then further

sub divided(ie. SUB_RAD) into 0, 1, 2 and 3. Each SUB_RAD will have its

count recorded.

In this case, I would like to get the max COUNT1 value of M101 by MAIN_RAD.

Row1 as shown in the table is the max COUNT1 value for MAIN_RAD 0 while

Row7 is for MAIN_RAD 1.

The expected outcome will choose and show the max COUNT1 value only in a

table that's similar to origin(with columns ID1, MAIN_RAD, SUB_RAD and

COUNT1). Meaning that, instead of 8 rows, the new table will have only 2

rows now.

Hope it's clearer now :)

Thanks.

Sorry for the confusion.

The ID1 group can be referred as SerialNo. Assuming that for M101(and M105

and M110 as well), it has 2 Main Radius(MAIN_RAD), 0 and 1. Then further

sub divided(ie. SUB_RAD) into 0, 1, 2 and 3. Each SUB_RAD will have its

count recorded.

In this case, I would like to get the max COUNT1 value of M101 by MAIN_RAD.

Row1 as shown in the table is the max COUNT1 value for MAIN_RAD 0 while

Row7 is for MAIN_RAD 1.

The expected outcome will choose and show the max COUNT1 value only in a

table that's similar to origin(with columns ID1, MAIN_RAD, SUB_RAD and

COUNT1). Meaning that, instead of 8 rows, the new table will have only 2

rows now.

Hope it's clearer now :)

Thanks.

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

Re: Finding the max value as per ID

I think this might be what you want. I used a formula to do the calculation, but if what I did is correct, there are several other ways to do it.

```
If( Lag( :ID1 ) == :ID1 & Lag( :MAIN_RAD ) == :MAIN_RAD,
.,
Col Max( :COUNT1, :ID1, :MAIN_RAD )
)
```

Jim

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

Re: Finding the max value as per ID

Thanks Jim. That works too! It will be great if I can get something like table below meaning the max value in col Maximun is in the same row as column Count1 max value.

ID1 | MAIN_RAD | SUB_RAD | COUNT1 | Maximum |

M101 | 0 | 0 | 1796 | 1796 |

M101 | 0 | 1 | 1583 | |

M101 | 0 | 2 | 1521 | |

M101 | 0 | 3 | 1621 | |

M101 | 1 | 0 | 1601 | |

M101 | 1 | 1 | 1539 | |

M101 | 1 | 2 | 1639 | 1639 |

M101 | 1 | 3 | 1610 | |

M105 | 0 | 0 | 1601 | |

M105 | 0 | 1 | 1539 | |

M105 | 0 | 2 | 1639 | 1639 |

M105 | 0 | 3 | 1610 | |

M105 | 1 | 0 | 1856 | |

M105 | 1 | 1 | 1960 | |

M105 | 1 | 2 | 1931 | |

M105 | 1 | 3 | 2004 | 2004 |

M110 | 0 | 0 | 1993 | 1993 |

M110 | 0 | 1 | 1779 | |

M110 | 0 | 2 | 1820 | |

M110 | 0 | 3 | 1813 | |

M110 | 1 | 0 | 1813 | 1813 |

M110 | 1 | 1 | 1736 | |

M110 | 1 | 2 | 1620 | |

M110 | 1 | 3 | 1643 |

BTW, I will be interested to know what will be the several ways to do it :)

Thanks. Appreciate that.

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

Here are 3 of the ways to do this.......other examples could be thought up

```
Names Default To Here( 1 );
dt = New Table( "Sample",
Add Rows( 24 ),
New Column( "ID1",
Character,
"Nominal",
Set Values(
{"M101", "M101", "M101", "M101", "M101", "M101", "M101", "M101", "M105", "M105", "M105", "M105", "M105",
"M105", "M105", "M105", "M110", "M110", "M110", "M110", "M110", "M110", "M110", "M110"}
)
),
New Column( "MAIN_RAD",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 1, 1, 1, 1] )
),
New Column( "SUB_RAD",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3] )
),
New Column( "COUNT1",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values(
[1796, 1583, 1521, 1621, 1601, 1539, 1639, 1610, 1601, 1539, 1639, 1610, 1856, 1960, 1931, 2004, 1993, 1779,
1820, 1813, 1813, 1736, 1620, 1643]
)
)
);
// Create the column using a formula
dt << New Column( "By Formula",
Character( 16 ),
"Nominal",
Formula(
If( Row() == 1, flag = 0 );
If( Lag( :ID1 ) != :ID1 | Lag( :MAIN_RAD ) != :MAIN_RAD,
flag = 0
);
If( flag == 0 & Col Max( :COUNT1, :ID1, :MAIN_RAD ) == :COUNT1,
Flag = 1;
"Max";
,
""
);
)
);
// Create the column using JSL
dt << New Column( "Using JSL", character );
For( i = 1, i <= N Rows( dt ), i++,
rowID1 = dt:ID1[i];
rowMain_Rad = dt:MAIN_RAD[i];
sameRows = dt << get rows where( dt:ID1 == rowID1 & dt:MAIN_RAD == rowMain_Rad );
If( Max( dt:Count1[sameRows] ) == :COUNT1[i],
dt:Using JSL[i] = "Max"
);
);
// Create the column using Platforms
dtMax = dt << Summary(
invisible,
Group( :ID1, :MAIN_RAD ),
Max( :COUNT1 ),
Freq( "None" ),
Weight( "None" ),
statistics column name format( "column" ),
Link to original data table( 0 )
);
dtMax << delete columns( "N Rows" );
dtMax << New Column( "Using Platform", character, set each value( "Max" ) );
dt << Update( With( dtMax ), Match Columns( :ID1 = :ID1, :MAIN_RAD = :MAIN_RAD, :COUNT1 = :COUNT1 ) );
```

Jim

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

Re: Finding the max value as per ID

Awesome Jim !!