Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Fetching field values for given set of conditions

Hi All,

I have a following kind of data.

IdNamePercentPass
1R18.4
2R18.5
3R18.8
4R18.7
1R27.8
2R27.9
3R27.4
4R27.5
1R38.5
2R35.8
3R38.3
4R38.2

Name and ID form the composite key.

I trying to achieve the following calculation:

  • First find maximum PercentPass Name wise
    • In this case, it would be : R1 - 8.8, R2 - 7.9, R3 - 8.5
  • Now store that Name in a variable Name_max having maximum PercentPass
    • In this case, it would be R1
  • Store Name in variable Name_max_2 having second maximum PercentPass
    • In this case, it would be R3

I am not able to figure a way to do this.

Please help.

Thanks in advance.

Regards,

Disha Ahuja

1 Solution

Accepted Solutions
sunny_talwar

Try these:

Name_max

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name))

Name_max_2

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name), 2)


Name_max_3

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name), 3)

View solution in original post

3 Replies
Anonymous
Not applicable
Author

try like this for Maximum?

=max(Aggr(max(PercentPass),Name))

sunny_talwar

Try these:

Name_max

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name))

Name_max_2

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name), 2)


Name_max_3

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name), 3)

Not applicable
Author

Thanks a lot for your help Sunny. This did me wonders!!