INTERVALMATCH for 2 intervals

[MindaugasBacius]

Hello,

I need to connect 2 tables.

Facts table:

Code	Weight	Date
1	4	2024-04-15
2	2	2024-04-15
1	15	2024-05-15

Master table:

Code	Date from	Date to	Weight from	Weight to	Line
1	2024-04-01	2024-04-30	0	5	X
1	2024-04-01	2024-05-31	6	10000	Y
1	2024-04-01	2024-04-30	6	10000	Z
1	2024-05-01	2024-05-31	6	10000	W
2	2024-04-01	2024-05-01	0	10000	X

Result:

Code	Weight	Date	Line
1	4	2024-04-15	X
2	4	2024-04-15	X
1	15	2024-05-15	Y
1	15	2024-05-15	W

The interval match should work for 2 intervals for Dates and Weight.

How could I accomplish this in the backend?

Thank you

[Antoine04 · Accepted solution]

Hello,

I tried with your DATA and the only way I see is to do 2 IntervalMatch and then define which row we should keep (I used an inner join in my script below).

Here is the table you expect :

And here is the script I wrote :

FACT:
Load * Inline [
Code_fact, Weight, Date
1, 4, 15/04/2024
2, 2, 15/04/2024
1, 15, 15/05/2024
];

DATA:
Load
 *,
 [Date from] & ' | ' & [Date to]  as IntervalMatch_Date,
 [Weight from] & ' | ' & [Weight to] as IntervalMatch_Weight
;
Load * Inline [
Code, Date from, Date to, Weight from, Weight to, Line
1, 01/04/2024, 30/04/2024, 0, 5, X
1, 01/04/2024, 31/05/2024, 6, 10000, Y
1, 01/04/2024, 30/04/2024, 6, 10000, Z
1, 01/05/2024, 31/05/2024, 6, 10000, W
2, 01/04/2024, 01/05/2024, 0, 10000, X
];

IM:
IntervalMatch(Date)
Load
 [Date from],
 [Date to]
Resident DATA;

Left Join (FACT)
Load
 Date,
 [Date from] & ' | ' & [Date to] as IntervalMatch_Date
Resident IM;
Drop Table IM;

IM2:
IntervalMatch(Weight)
Load
 [Weight from],
 [Weight to]
Resident DATA;

Left Join (FACT)
Load
 Weight,
 [Weight from] & ' | ' & [Weight to] as IntervalMatch_Weight
Resident IM2;
Drop Table IM2;

Inner Join (FACT)
Load Distinct
 Code as Code_fact,
 IntervalMatch_Date,
 IntervalMatch_Weight,
 Line
Resident DATA;
Drop Table DATA;

Hope it helps.

Regards,

Antoine