Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

firstsortedvalue and concat

I am trying to add an expression in a chart that shows 'Unit' grouped by Product with the max truckQty (and max unitQty). Or even concat multiple results if it returns more than 1 result for max truckQty.

Sample Data:

LOAD * INLINE [
Product, Unit, truckQty, unitQty
a, aa, 10000, 100
a, bb, 5000, 200
a, cc, 7000, 100
a, dd, 10000, 250
b, aa, 10000, 200
b, bb, 8000, 300
b, cc, 5000, 250
b, dd, 11000, 250
]
;

FirstSortedValue(distinct Unit, -truckQty) returns the following as expected.

a | aa

b | dd

However, my question is how do I get 'a' to return 'dd' (largest truck qty and largest unit qty). I have tried the aggr function but have not been able to get the expected results.

Thank you in advance for taking the time to help me....

1 Solution

Accepted Solutions
sunny_talwar

You can use this

=Concat(DISTINCT Aggr(If(truckQty = Max(TOTAL <Product> truckQty), Unit), Product, Unit), ', ')

View solution in original post

6 Replies
sunny_talwar

May be like this

FirstSortedValue(Distinct Unit, -(truckQty+(unitQty/1E10)))

sunny_talwar

Sample attached

Capture.PNG

Not applicable
Author

That works, thank you so much.

One more question... what if I wanted to include both values for max truckQty of 'a' as 'dd,aa' ?

sunny_talwar

You can use this

=Concat(DISTINCT Aggr(If(truckQty = Max(TOTAL <Product> truckQty), Unit), Product, Unit), ', ')

Not applicable
Author

Brilliant! Thank you for your help and for responding so quickly.

FranAgraz
Contributor
Contributor

Hello sunny. Is there any more performant way to do that? Thank you very much.