Highest count

[#1]

Hi guys need some help please.

I have list of SuperKey's that have more than one Route-ID. Basically I only need to see the line with highest count which in this case is line 1. How can I achieve this?

SuperKey	ROUTE_ID	RouteID Count
USAA2000045621OS030C2610	WHG03	528
USAA2000045621OS030C2610	WHG02	192

Regards,

Raj

[Gysbert_Wassenaar · Accepted solution]

Try these two expressions:

=Max([RouteID Count])

=FirstSortedValue(ROUTE_ID, -[RouteID Count])

[sunny_talwar]

May be do something like this:

Table:

LOAD SuperKey,

          ROUTE_ID

          [RouteID COUNT]

FROM Source;

Right Join(Table)

LOAD SuperKey,

          Max([RouteID COUNT]) as [RouteID COUNT]

Resident Table

Group By SuperKey;

[#4]

Hi both thank you for the quick reply highly appreciate it. I am still working on it as there are some changes in the requirement. I will soon check and let u know if i am successful

Thanks a lot

[#5]

Thanks guys both solutions are great and first one fits my current requirement.

Sunny T solution may not be used for now but will definitely be useful in future.

[sunny_talwar]

Glad we were able to help