Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
flygstolen_fred
Creator
Creator

Resident load of fields from multiple tables

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:

InvoiceIdInvoiceDateTimeReservationId
1232018-07-01T11:43:00.595
1242018-07-01T12:10:00.115
1252018-07-02T08:14:00.235
1262018-07-02T08:15:00.446

Reservation:

ReservationIdReservationDateTIme
52018-07-01T11:43:00.72
62018-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;

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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
12018-07-01T11:43:00.591235
2018-07-01T12:10:00.111245
2018-08-02T08:14:00.231255
12018-07-02T08:15:00.441266

Reportdate 2018-08-01

Flag InvoiceDateTime InvoiceId ReservationId
2018-08-02T08:14:00.231255

View solution in original post

8 Replies
swuehl
MVP
MVP

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
Creator
Creator
Author

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
MVP
MVP

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
Creator
Creator
Author

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
Creator
Creator
Author

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:

InvoiceIdInvoiceDateTimeReservationId
1232018-07-01T11:43:00.595
1242018-07-01T12:10:00.115
1252018-08-02T08:14:00.235
1262018-07-02T08:15:00.446

Reservation:

ReservationIdReservationDateTIme
52018-07-01T11:43:00.72
62018-07-02T08:15:00.48
timpoismans
Specialist
Specialist

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
MVP
MVP

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
12018-07-01T11:43:00.591235
2018-07-01T12:10:00.111245
2018-08-02T08:14:00.231255
12018-07-02T08:15:00.441266

Reportdate 2018-08-01

Flag InvoiceDateTime InvoiceId ReservationId
2018-08-02T08:14:00.231255
flygstolen_fred
Creator
Creator
Author

Many thanks Stefan!!