Intervalmatch ?

[xyz_1011]

Hej all,

i am trying to join two tables into one, but i cannot figure out, how. Here are my two source tables:

Note, that periods in table CONTRACTS can cover multiple periods in table OFFERS for one given contract. That is the case, when the discount does not change over more than one period in table OFFERS. The desired result table should look like this:

Basically i want to attach to every record of a given contract and period in table OFFERS the corresponding record from table CONTRACTS. How would you do this ?

Thanks a lot in advance for your help!

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

[rubenmarin]

Hi, if using only OFFER_START can be used to setthe contract period, you can follows the example 2 of the help page, using CONTRACT_ID as the keyfield.

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

[MeehyeOh · Accepted solution]

Hi, @xyz_1011 

Refer to my test. 

1. Load Script
(In my case, I need to convert to Date format because it is data made from Inline.)

CONTRACT:

Load

CONTRACT_ID,

    Date(Date#(CONTRACT_START,'YYYY.MM.DD'),'YYYY.MM.DD') As CONTRACT_START,

    Date(Date#(CONTRACT_END,'YYYY.MM.DD'),'YYYY.MM.DD') As CONTRACT_END,

    CONTRACT_DISCOUNT

;

Load * Inline [ 

CONTRACT_ID, CONTRACT_START, CONTRACT_END, CONTRACT_DISCOUNT

7964, 2023.01.01, 2024.12.31, 3

7964, 2025.01.01, 9999.12.31, 2

8654, 2024.06.15, 9999.12.31, 4

];

OFFER:

Load

CONTRACT_ID,

    Date(Date#(OFFER_START,'YYYY.MM.DD'),'YYYY.MM.DD') As OFFER_START,

    Date(Date#(OFFER_END,'YYYY.MM.DD'),'YYYY.MM.DD') As OFFER_END,

    OFFER_DISCOUNT

;

Load * Inline [ 

CONTRACT_ID, OFFER_START, OFFER_END, OFFER_DISCOUNT

7964, 2023.01.01, 2023.12.31, 3

7964, 2024.01.01, 2024.12.31, 3

7964, 2025.01.01, 2025.12.31, 2

8654, 2024.06.15, 2025.06.14, 4

8654, 2025.06.15, 2026.06.14, 4

];

Left Join IntervalMatch(OFFER_START,CONTRACT_ID)

Load

    CONTRACT_START,

    CONTRACT_END,

CONTRACT_ID

Resident CONTRACT;

Left Join(OFFER)

Load

CONTRACT_ID,

    CONTRACT_START,

    CONTRACT_DISCOUNT

Resident CONTRACT;

Drop Table CONTRACT;

Rename Table OFFER to RESULT;

2. Table 

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download