Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
Not applicable

Re: Excel array functionality in Qlik Sense

Sorry, My bad I missed to add Group by

Left Join(Order)

Load

   OrderNo

   Min (TransactionNo)as FirstTransaction

  Resident Order

Group By OrderNo;

4 Replies
celambarasan
Not applicable

Re: Excel array functionality in Qlik Sense

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

Re: Excel array functionality in Qlik Sense

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
Not applicable

Re: Excel array functionality in Qlik Sense

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

Re: Excel array functionality in Qlik Sense

That seems to work.  Thanks!