Subscribe Bookmark RSS Feed

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

chrisATX1

Community Trekker

Joined:

Jun 7, 2016

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

Joined:

Jun 23, 2011

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.

 

chrisATX1

Community Trekker

Joined:

Jun 7, 2016

Solution

Hi Ron -

 

Thanks!  This works perfectly - appreciate the help!

 

-Chris

 

4 REPLIES
louv

Staff

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Jun 7, 2016

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

Joined:

Jun 23, 2011

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.

 

chrisATX1

Community Trekker

Joined:

Jun 7, 2016

Solution

Hi Ron -

 

Thanks!  This works perfectly - appreciate the help!

 

-Chris