Joining Component to Product depending on date of production

[kchamot]

Hello,

I have trouble in joining Component to Product depending on date of production

Table A is data about production date

Product	ProductionDate
AAA	01.01.2023
AAA	01.01.2024
AAA	01.06.2024
AAA	01.10.2024

Table B is data about component that was used depending on date

Product	Component	From	To
AAA	BB1	01.01.2024	01.04.2024
AAA	BB2	02.04.2024	01.09.2024
AAA	BB3	02.09.2024

Outcome should look like:

Product	ProductionDate	Component
AAA	01.01.2023	BB1
AAA	01.01.2024	BB1
AAA	01.06.2024	BB2
AAA	01.10.2024	BB3

What is the best approach to doing this ?

[Kushal_Chawda · Accepted solution]

@kchamot  checkout IntervalMatch

[Qrishna · Accepted solution]

dat1:
LOAD
Product,
Date(Date#(ProductionDate, 'DD.MM.YYYY'), 'DD.MM.YYYY') as ProductionDate
INLINE [
Product, ProductionDate
AAA, 01.01.2023
AAA, 01.01.2024
AAA, 01.06.2024
AAA, 01.10.2024
];

dat2:
LOAD
Product as prod,
Component,
Date(Date#(From, 'DD.MM.YYYY'), 'DD.MM.YYYY') as From,
Date(Date#(To, 'DD.MM.YYYY'), 'DD.MM.YYYY') as To
INLINE [
Product, Component, From, To
AAA, BB1, 01.01.2024, 01.04.2024
AAA, BB2, 02.04.2024, 01.09.2024
AAA, BB3, 02.09.2024, 01.10.2024
];

// Create an IntervalMatch to link ProductionDate with From-To ranges
join IntervalMatch (ProductionDate)
LOAD
From,
To
RESIDENT dat2;

i dont see

AAA	01.01.2023

 falling between any of the row's from-to dates.

Output: