Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey,
I'm trying to add a field to my Invoice table if the invoice is the first created in every reservation or not.
I'm not sure how I can use ReservationDateTime when I try to compare ReservationDateTime against InvoiceDateTime?
Any help is very appreciated.
Invoice:
InvoiceId | InvoiceDateTime | ReservationId |
---|---|---|
123 | 2018-07-01T11:43:00.59 | 5 |
124 | 2018-07-01T12:10:00.11 | 5 |
125 | 2018-07-02T08:14:00.23 | 5 |
126 | 2018-07-02T08:15:00.44 | 6 |
Reservation:
ReservationId | ReservationDateTIme |
---|---|
5 | 2018-07-01T11:43:00.72 |
6 | 2018-07-02T08:15:00.48 |
Invoice:
LOAD * from Invoice
WHERE DATEDIFF(day,'$(LoadDataFromDate)', InvoiceDateTime)>=0;
Reservation:
LOAD * from Reservation r
INNER JOIN Invoice i ON r.ReservationId = i.ReservationId
WHERE DATEDIFF(day,'$(LoadDataFromDate)', i.InvoiceDateTime)>=0;
/* HERE I'm trying to solve the problem without success */
LEFT JOIN (Invoice)
LOAD InvoiceId,
IF (MIN(InvoiceDateTime) = ReservationDateTime, 1, 0) as IsInitialInvoice
RESIDENT Invoice GROUP BY InvoiceId;
Maybe consider an incremental load strategy if you don't want to load all data from your invoice source (using QVDs to store historic data).
You can also restrict the incremental load to a table that shows all first invoices (as you said, the flag of a first invoice shouldn't change when changing report date).
Or you can use the information of the reservation table, I guess that's something you have tried in your original post:
// Set the report data, modifiable
Let ReportDate = '2018-08-01';
//Only consider reservations from the report date on
Reservations:
LOAD ReservationId,
ReservationId as ReservationTimeCheck,
ReservationDateTIme
FROM
[https://community.qlik.com/thread/318045]
(html, codepage is 1252, embedded labels, table is @4)
WHERE ReservationDateTIme >= '$(ReportDate)';
//Only consider in invoices from the report date on
Invoice:
LOAD InvoiceId,
InvoiceDateTime,
ReservationId
FROM
[https://community.qlik.com/thread/318045]
(html, codepage is 1252, embedded labels, table is @3)
WHERE InvoiceDateTime >= '$(ReportDate)';
// Only flag the min(InvoiceId) of a reservation, if the reservation is also within the captured date range
Left JOIN (Invoice)
LOAD Min(InvoiceId) as InvoiceId,
Only(If(Exists(ReservationTimeCheck, ReservationId),1)) as Flag
Resident Invoice Group by ReservationId;
// You may DROP FIELD ReservationTimeCheck; at the end
Reportdate 2018-07-01:
Flag | InvoiceDateTime | InvoiceId | ReservationId |
---|---|---|---|
1 | 2018-07-01T11:43:00.59 | 123 | 5 |
2018-07-01T12:10:00.11 | 124 | 5 | |
2018-08-02T08:14:00.23 | 125 | 5 | |
1 | 2018-07-02T08:15:00.44 | 126 | 6 |
Reportdate 2018-08-01
Flag | InvoiceDateTime | InvoiceId | ReservationId |
---|---|---|---|
2018-08-02T08:14:00.23 | 125 | 5 |
Your current code is not valid Qlik script code, I guess you know this already.
I am still trying to figure out what you want to do or expect as result from your sample data set.
None of your InvoiceDateTime values is matching any of the ReservationDateTime, so ... you are expecting no Invoice record to be flagged with 1, is this correct?
edit: I am asking because of your condition
IF (MIN(InvoiceDateTime) = ReservationDateTime, 1, 0)
If you are instead looking for the first created invoice in every reservation, do you need the ReservationDateTime at all?
Wouldn't it be sufficient to find the min InnvoiceDateTime per ReservationID and flag its InvoiceID record?
Thanks Stefan for trying to help.
Yes, I reduced the script code a bit to be less confusing.
I want the first created invoice to be flagged with 1, all others with 0.
If I generate the report with $(LoadDataFromDate) as 2018-07-01 I want invoiceId 123 & 126 to be flagged 1.
If I generate the report $(LoadDataFromDate) as 2018-07-02 I only want InvoiceId 126 to be flagged 1.
Does my explanation make sense?
Maybe something like
Invoice:
LOAD * FROM Invoice;
LEFT JOIN (Invoice)
LOAD InvoiceId, 1 as Flag
WHERE MinInvoiceDateTime >= '$(LoadDateFromDate)';
LOAD FirstSortedValue(InvoiceId, InvoiceDateTime) as InvoiceId,
Min(InvoiceDateTime) as MinInvoiceDateTime
RESIDENT Invoice GROUP BY ReservationId;
Thanks again Stefan for your help.
My Invoice-table is very large and don't want download all data, only within a certain date range.
I will generate the report monthly and I want to be able to separate if the Invoice was the first in every Reservation or if it's additional invoices.
There will situations when the reservation and first invoice are in one month and the additional invoices in another.
Ideally would be to check if the ReservationDateTime and InvoiceDateTime is the same date and then set the MIN(InvoiceDateTime) to Flag 1. All other to Flag 0.
Hi again,
For example, if I want to generate a report from August, then only invoice "125" will be valid, and that is not the first invoice created in Reservation 5, => Invoice 125 will get Flag 0.
Does this explain it better?
Invoice:
LOAD * from Invoice
WHERE InvoiceDateTime >= '2018-08-01';
Invoice:
InvoiceId | InvoiceDateTime | ReservationId |
---|---|---|
123 | 2018-07-01T11:43:00.59 | 5 |
124 | 2018-07-01T12:10:00.11 | 5 |
125 | 2018-08-02T08:14:00.23 | 5 |
126 | 2018-07-02T08:15:00.44 | 6 |
Reservation:
ReservationId | ReservationDateTIme |
---|---|
5 | 2018-07-01T11:43:00.72 |
6 | 2018-07-02T08:15:00.48 |
You'll have to load the entire dataset to achieve this.
In your example, 125 is the only valid InvoiceId, the only row loaded. But that way, you can't know that it's not the first InvoiceId of that ReservationId.
So you'll have to load the entire data table (or limit it based on years if you want to reduce the load), create the Flag and then limit the data, e.g. a report from August.
Maybe consider an incremental load strategy if you don't want to load all data from your invoice source (using QVDs to store historic data).
You can also restrict the incremental load to a table that shows all first invoices (as you said, the flag of a first invoice shouldn't change when changing report date).
Or you can use the information of the reservation table, I guess that's something you have tried in your original post:
// Set the report data, modifiable
Let ReportDate = '2018-08-01';
//Only consider reservations from the report date on
Reservations:
LOAD ReservationId,
ReservationId as ReservationTimeCheck,
ReservationDateTIme
FROM
[https://community.qlik.com/thread/318045]
(html, codepage is 1252, embedded labels, table is @4)
WHERE ReservationDateTIme >= '$(ReportDate)';
//Only consider in invoices from the report date on
Invoice:
LOAD InvoiceId,
InvoiceDateTime,
ReservationId
FROM
[https://community.qlik.com/thread/318045]
(html, codepage is 1252, embedded labels, table is @3)
WHERE InvoiceDateTime >= '$(ReportDate)';
// Only flag the min(InvoiceId) of a reservation, if the reservation is also within the captured date range
Left JOIN (Invoice)
LOAD Min(InvoiceId) as InvoiceId,
Only(If(Exists(ReservationTimeCheck, ReservationId),1)) as Flag
Resident Invoice Group by ReservationId;
// You may DROP FIELD ReservationTimeCheck; at the end
Reportdate 2018-07-01:
Flag | InvoiceDateTime | InvoiceId | ReservationId |
---|---|---|---|
1 | 2018-07-01T11:43:00.59 | 123 | 5 |
2018-07-01T12:10:00.11 | 124 | 5 | |
2018-08-02T08:14:00.23 | 125 | 5 | |
1 | 2018-07-02T08:15:00.44 | 126 | 6 |
Reportdate 2018-08-01
Flag | InvoiceDateTime | InvoiceId | ReservationId |
---|---|---|---|
2018-08-02T08:14:00.23 | 125 | 5 |
Many thanks Stefan!!