Maximum count using AGGR function

[Anonymous]

Hi,

I have a requirement wherein I have to get the the maximum count in a chart and display related values for it.

I am able to get the Maximum count but not sure, how to get the related values.

Here is my sample requirement.

  CN: Claim Number    CLN: Claim Line Number    POS: Place of Service    LOS: Level of Service

CN	CLN	POS	LOS
12345	123451	21	M
12345	123452	11	N
12345	123453	11	N
12345	123454	11	N
23456	234561	14	P
23456	234562	14	P
23456	234563	19	B
23456	234564	19	B
23456	234565	19	B
34567	345671	11	N
34567	345672	11	N
34567	345673	11	N
45678	456781	22	C
45678	456782	22	C
45678	456783	25	D
45678	456784	25	D

I have to get POS that is most common in each Claim and get the POS and LOS.

So output should be

CN	Count of POS	POS	LOS
12345	3	11	N
23456	3	19	B
34567	3	11	N
45678	2	22,25	C,D

I am able to get the maximum Count of POS (2nd column) by using the following expression

MAX(AGGR(COUNT(POS),CN,POS)

But I am not able to get the related POS and LOS.

Is there anyway to get it from the chart itself instead of doing a COUNT and GROUP BY in the script.

Any help would be appreaciated.

Thanks,

Srikanth

[mayankraoka]

Hi Srikanth,

If possible please attach sample qvw so that we can help you quickly.And what number you are expecting here?

Regards,

Mayank

[tresesco]

May be like this?

=Concat(DISTINCT Aggr(If(Count(POS) = Max(TOTAL <CN> Aggr(Count(POS), CN,POS)), POS), CN,POS), ',')

=Concat(DISTINCT Aggr(If(Count(POS) = Max(TOTAL <CN> Aggr(Count(POS), CN,POS)), LOS), CN,POS), ',')

[Anonymous]

Hi,

max(Aggr(Count(POS),POS,CN))

FirstSortedValue({<CLN={"=max(Aggr(Count(POS),POS,CN))"}>}POS,-CLN,1)

FirstSortedValue({<CLN={"=max(Aggr(Count(POS),POS,CN))"}>}LOS,-CLN,1)

[Anonymous]

Thank you Tresesco!!

It worked. I was just using the below function while I was trying without the TOTAL qualifier.

Max(Aggr(Count(POS), CN,POS))

I guess the TOTAL qualifier with TOTAL<CN> aggregates with just the CN field.

Thanks,

Srikanth

[michaelrosen]

Thank you, Very cool!