Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Trying to replicate the excel functionality of an array function of: =MIN(IF($AJ$4:$AJ$50002=AJ2,$AK$4:$AK$50002)) in Qlik Sense. AJ is a variable, e.g., order number and I'm seeking to find the minimum chronological transaction number in AK. Basically want to return the first (min) transaction number in each record of a given order number. Help!
Sorry, My bad I missed to add Group by
Left Join(Order)
Load
OrderNo
Min (TransactionNo)as FirstTransaction
Resident Order
Group By OrderNo;
Have Order number as dimension and Min(TransactionNumber) as expression. It will give you the desired results.
If you want to do in script. try the below logic
Order:
Load
OrderNo,
TransactionNo
From ....
Left Join(Order)
Load OrderNo,
Min(TransactionNo) as FirstTransaction
Resident Order;
Hope this helps
Thanks for the response. After digging in I may have a complicating factor that is causing the below to bomb out with the subsequent Qlik Sense error. My order number ends up having to be the combination of two precursors, Order!&OrderB making up a created OrderNo.
Order:
LOAD
OrderA&OrderB as OrderNo,
TransactionNo
FROM [lib://data/activity.qvd]
(qvd);
Left Join(Order)
Load
OrderNo
Min (TransactionNo)as FirstTransaction
Resident Order;
The following error occurred:
Invalid expression
The error occurred here:
Left Join(Order) Load OrderNo, Min (TransactionNo)as FirstTransaction Resident Order
Sorry, My bad I missed to add Group by
Left Join(Order)
Load
OrderNo
Min (TransactionNo)as FirstTransaction
Resident Order
Group By OrderNo;
That seems to work. Thanks!