Compare Dates from one table with Start and End date in another table

[varunreddy]

@Hi All,

Hope you are doing good!

I am in a scenario where I have two tables (i.e. Summary and Metadata).

Summary has ID and Date.

Metadata has ID, Start Date, End Date and Country.

Metadata to Summary has One to Many relationship. Here "ID" is the joining Key between the tables..

Now I am trying to compare "Date" from Summary table with "Start Date" and "End Date" in Metadata table based on "ID" and display Country based on the Date which falls in between Start Date and End Date.

Summary:

Metadata:

Expected Output:

Here for ID - 1, The Country is UK becuase the Date is 5/20/2016 which is in between (5/10/2016 and 6/30/2016) -- Row1

ID - 1, The Country is India becuase the Date is 7/15/2016 which is in between (7/1/2016 and 12/31/9999) -- Row2

Please find the excel document below.

Thanks in advance!

Cheers,

Varun Reddy.

[swuehl]

You should be able to use

IntervalMatch

in the extended syntax:

IntervalMatch (Extended Syntax) ‒ QlikView

[sunny_talwar]

How did you come up with ID = 4? I got this:

Does it look right?

[varunreddy]

Hi Sunny,

This is what I want, For ID 4, date should be >8/19. That was typo error.

How did you get this?

[sunny_talwar · Accepted solution]

Script:

Table:

LOAD ID,

    [Start Date],

    [End Date],

    Country

FROM

[Book1 (12).xlsx]

(ooxml, embedded labels, table is Metadata);

Table2:

LOAD ID,

    Date

FROM

[Book1 (12).xlsx]

(ooxml, embedded labels, table is Summary);

Left Join (Table)

IntervalMatch(Date, ID)

LOAD [Start Date],

    [End Date],

    ID

Resident Table;

Left Join (Table)

LOAD *

Resident Table2;

DROP Table Table2;

[varunreddy]

Hi Stefan,

I tried something like this:

Summary:
LOAD ID,
     Date
FROM
C:\Users\43917679\Desktop\Book1.xlsx
(ooxml, embedded labels, table is Summary);

Metadata:
LOAD ID,
     [Start Date],
     [End Date],
     Country
FROM
C:\Users\43917679\Desktop\Book1.xlsx
(ooxml, embedded labels, table is Metadata);

Bridge:

IntervalMatch(Date,ID)
Load
ID,
[Start Date],
[End Date]
Resident Metadata;

I don't see Bridge table after reload:

Am I doing something wrong?

[varunreddy]

Thanks Sunny and Stefan. I tried Intrval Match but I didn't know that we have Extended Syntax in Interval Match.

Thanks for your quick responses.

Cheers,

Varun Reddy

[swuehl]

Try changing the order of your fields (and check all dates are showing numeric representation)

Bridge:

IntervalMatch(Date,ID)
Load
[Start Date],
[End Date] ,

ID

RESIDENT

[sunny_talwar]

No problem at all, always happy to help