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

[#1]

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]

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.

[#3]

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

[#4 · Accepted 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.

[#5 · Accepted solution]

Hi Ron -

Thanks!  This works perfectly - appreciate the help!

-Chris