Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | 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 |
|
Order:
LOAD OrderId,
CountryCode,
Referer,
Total,
OrderDate
Invoice:
LOAD InvoiceId,
OrderId
InvoiceDate
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.
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.
Thanks for the help undergrinder. Works perfect!