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

Excel array functionality in Qlik Sense

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!

1 Solution

Accepted Solutions
CELAMBARASAN
Partner - Champion
Partner - Champion

Sorry, My bad I missed to add Group by

Left Join(Order)

Load

   OrderNo

   Min (TransactionNo)as FirstTransaction

  Resident Order

Group By OrderNo;

View solution in original post

4 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

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

Not applicable
Author

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

CELAMBARASAN
Partner - Champion
Partner - Champion

Sorry, My bad I missed to add Group by

Left Join(Order)

Load

   OrderNo

   Min (TransactionNo)as FirstTransaction

  Resident Order

Group By OrderNo;

Not applicable
Author

That seems to work.  Thanks!