cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
chrisATX1
Level II

How do I iterate through rows to find most common and max values?

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:

Sample Table.JPGThe 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
ron_horne
Super User (Alumni)

Re: How do I iterate through rows to find most common and max values?

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.

 

View solution in original post

chrisATX1
Level II

Re: How do I iterate through rows to find most common and max values?

Hi Ron -

 

Thanks!  This works perfectly - appreciate the help!

 

-Chris

 

View solution in original post

4 REPLIES 4
louv
Staff (Retired)

Re: How do I iterate through rows to find most common and max values?

Not sure if this helps but I used Tables>Summary and then used Field 1 and Field 2 as Grouping variable and Max as the statistic.

 

Screen Shot 2017-03-30 at 4.11.32 PM.png

chrisATX1
Level II

Re: How do I iterate through rows to find most common and max values?

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

ron_horne
Super User (Alumni)

Re: How do I iterate through rows to find most common and max values?

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.

 

chrisATX1
Level II

Re: How do I iterate through rows to find most common and max values?

Hi Ron -

 

Thanks!  This works perfectly - appreciate the help!

 

-Chris