Resident load of fields from multiple tables

[flygstolen_fred]

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;

[swuehl]

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?

[flygstolen_fred]

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?

[swuehl]

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;

[flygstolen_fred]

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.

[flygstolen_fred]

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

[timpoismans]

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.

[swuehl · Accepted solution]

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

[flygstolen_fred]

Many thanks Stefan!!