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

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

chrisATX1 — Fri, 09 Jun 2023 23:23:46 GMT

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!

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

louv — Thu, 30 Mar 2017 20:14:53 GMT

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.

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

chrisATX1 — Thu, 30 Mar 2017 20:36:36 GMT

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

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

ron_horne — Thu, 30 Mar 2017 21:44:16 GMT

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.

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

chrisATX1 — Fri, 31 Mar 2017 23:07:12 GMT

Hi Ron -

Thanks! This works perfectly - appreciate the help!

-Chris