Finding values in one field, which is associated to maximum value of another

[#1]

Below is the Data:

Contact	Location	Calls
Con	A	2
Con	B	1
Con1	A	1
Con1	B	1
Con2	B	2

I want number of calls per contact based on minstring of location'.

Output:

Contact	Location	Calls
Con	A	2
Con1	A	1
Con2	B	2

[rubenmarin · Accepted solution]

Hi Ramesh, if you have a table with Contact and Location as dimension, you can ause this expression:

If(Location=MinString(TOTAL <Contact> Location), Calls)

Check 'Supress zero values'

[sunny_talwar]

Try this script:

Table:

LOAD Contact,

    Location,

    Calls

FROM

[https://community.qlik.com/thread/165913]

(html, codepage is 1252, embedded labels, table is @1);

Right Join(Table)

LOAD Contact,

  Max(Calls) as Calls

Resident Table

Group By Contact;

[#4]

Hi Ruben,

Thanks for the response, With Contact and Location as dimension, we can achieve same with below expression:

if( Location=MinString( aggr(MinString(Location), Contact)), sum(Calls))

-Regards

Ramesh