topic Re: firstsortedvalue and concat in QlikView

firstsortedvalue and concat

Thu, 27 Apr 2017 14:05:10 GMT

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....

Re: firstsortedvalue and concat

sunny_talwar — Thu, 27 Apr 2017 14:11:47 GMT

May be like this

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

Re: firstsortedvalue and concat

sunny_talwar — Thu, 27 Apr 2017 14:15:36 GMT

Sample attached

Re: firstsortedvalue and concat

Thu, 27 Apr 2017 14:19:17 GMT

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' ?

Re: firstsortedvalue and concat

sunny_talwar — Thu, 27 Apr 2017 14:22:20 GMT

You can use this

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

Re: firstsortedvalue and concat

Thu, 27 Apr 2017 14:34:00 GMT

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

Re: firstsortedvalue and concat

FranAgraz — Fri, 27 Sep 2019 19:22:30 GMT

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