Excel array functionality in Qlik Sense

[#1]

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!

[CELAMBARASAN]

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

[#3]

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 · Accepted solution]

Sorry, My bad I missed to add Group by

Left Join(Order)

Load

   OrderNo

   Min (TransactionNo)as FirstTransaction

  Resident Order

Group By OrderNo;

[#5]

That seems to work.  Thanks!