Group by Region wise, need Max Quantity and corresponding name

[#1]

Hi All,

 
Please help me on the below requirement.
 
i have a table like this.
 

Region	Name	Quantity
a	aa	5
a	bb	5
a	cc	3
b	dd	3
c	ee	2

i want the output like below format... which is that region wise, maximum quantity and the corresponding person who achieved that.

Region	Name	Quantity
a	aa,bb	5
b	dd	3
c	ee	2

please help in formulating the expression to obtain the Name for the max quantity.



Thanks in advance,

[Gysbert_Wassenaar]

Here's one:

Concat(distinct if(Quantity=aggr(nodistinct max(Quantity),Region), Name),',')

[svsudhakar]

Hi Gysbert,

Please find attached application with a replica of actual scenario. In this case, how can we modify the expression you have provided so that we can get the corresponding names against the maximum count of quantity in that region.

We'll have to find the count of Quantity against every region every person in that region and find the person(s) who has(have) the maximum in that region.

Thanks again...

-Sudhakar

[#4]

PFA Application

--Amay

[svsudhakar]

Hi Gysbert,

Please ignore the above application and consider the attached application with this post.

I have modified the Quantity to Id as we have to count the IDs against the Name and the Region.

Kindly forgive to change the scenario, but this is what is required.

Thanks and Regards,

SVS

[Gysbert_Wassenaar · Accepted solution]

See attached qvw. If that's not what you need please post tables that show what the result should look like.

[#7]

Thanks Gysbert,

That's a nice solution...  the count and names got are perfectly correct.

Now that suppose, the following lines are included in the data table, then for region c, both the persons should be attributed with the count 2.

Region, Name, Id, status, lob

c, gg, 29 ,0  ,LS

c, gg, 30 ,0  ,LS

As you mentioned, the following would then be the output:

Region	Name with maximum count	Maximum count
a	aa	4
b	dd	3
c	ee, gg	2
d	aa	7

As in the first reply you provided, can we do some restrictions in the concat function, that seemed more promising to get the solution.

Thanks and Regards,

K