Add flag to first nested item

[flygstolen_fred]

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:

OrderId	CountryCode	Referer	Total	OrderDate
1234	SE	GOOGLE	330	2016-01-05
1235	NO	NEWSLETTER	105	2016-01-15

Invoice:

InvoiceId	OrderId	InvoiceDate
789	1234	2016-01-05
790	1234	2016-01-07
791	1235	2016-01-15

Order:

LOAD OrderId,

    CountryCode,

    Referer,

    Total,

    OrderDate

Invoice:

LOAD InvoiceId,

    OrderId

    InvoiceDate

[undergrinder · Accepted solution]

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]

Thanks for the help undergrinder. Works perfect!