Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: firstsortedvalue and concat

You can use this

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

5 Replies
MVP
MVP

Re: firstsortedvalue and concat

May be like this

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

MVP
MVP

Re: firstsortedvalue and concat

Sample attached

Capture.PNG

Not applicable

Re: firstsortedvalue and concat

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

MVP
MVP

Re: firstsortedvalue and concat

You can use this

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

Not applicable

Re: firstsortedvalue and concat

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