Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

flygstolen_fred
New Contributor III

Add flag to first nested item

Hi,

In my load statement I wanna add a flag on Invoice level if the its the first invoice (based on invoicedate) in every Order.

Any help would be much appreciated.

Is it possible to do something like:

if (min(InvoiceDate) ?, 1, 0) as InitialInvoice

Order:

OrderIdCountryCodeRefererTotalOrderDate
1234SEGOOGLE3302016-01-05
1235NONEWSLETTER1052016-01-15

Invoice:

InvoiceIdOrderIdInvoiceDate
78912342016-01-05
79012342016-01-07
7911235
2016-01-15

Order:

LOAD OrderId,

    CountryCode,

    Referer,

    Total,

    OrderDate

Invoice:

LOAD InvoiceId,

    OrderId

    InvoiceDate

1 Solution

Accepted Solutions
undergrinder
Valued Contributor II

Re: Add flag to first nested item

Hi Fredrik,

As far as I get it, the following code will help you:

Order:

LOAD OrderId,

    CountryCode,

    Referer,

    Total,

    OrderDate

Invoice:

LOAD InvoiceId,

    OrderId

    InvoiceDate

Left join(Invoice)

Load OrderId

        ,min(InvoiceDate) as InvoiceDate

       ,'1' as Flg

Resident Invoice

Group by OrderId;

Where the minimum date is occurs by OrderId, the Flg columns contains 1 else null or missing value.

G.

2 Replies
undergrinder
Valued Contributor II

Re: Add flag to first nested item

Hi Fredrik,

As far as I get it, the following code will help you:

Order:

LOAD OrderId,

    CountryCode,

    Referer,

    Total,

    OrderDate

Invoice:

LOAD InvoiceId,

    OrderId

    InvoiceDate

Left join(Invoice)

Load OrderId

        ,min(InvoiceDate) as InvoiceDate

       ,'1' as Flg

Resident Invoice

Group by OrderId;

Where the minimum date is occurs by OrderId, the Flg columns contains 1 else null or missing value.

G.

flygstolen_fred
New Contributor III

Re: Add flag to first nested item

Thanks for the help undergrinder. Works perfect!

Community Browser