IntervalMatch with a JOIN

[de]

Hi,

I have two tables:
    One has an OrderDate and a TerritoryID (fact table)
    The other has a TerritoryID, EmployeeID, StartDate and EndDate (dim table).

I want to join the two tables based on territory, but also the start and end dates need to be between the OrderDate (SCD).
I tried interval matching with a resident load and joining it to the fact table and I also checked all the other forum answers, but none worked as I wanted it.

I'm still a Qlik novice and I hope you understand what I'm wondering.

Thank you!

[chrismarlow · Accepted solution]

Hi,

You could try the toy script below. I think in theory an interval match should also be possible, but can be hard to follow (I find them hard to follow), this may be less efficient, but if it works ...

Cheers,

Chris.

fact:
LOAD * INLINE [
    OrderDate, TerritoryID
    01/01/2019, 1
    01/02/2019, 1
    01/01/2019, 2
];

dim:
LOAD * INLINE [
 TerritoryID, EmployeeID, StartDate, EndDate
	1, 1, 15/12/2018, 15/01/2019
	1, 2, 15/01/2019, 15/02/2019
	2, 3, 15/12/2018, 15/01/2019
];

left join (fact)
Load
	*
Resident dim;

fact_final:
NoConcatenate
Load
	*
Resident fact
Where OrderDate>StartDate and OrderDate<=EndDate;

Drop tables fact, dim;

[de]

Hi, Chris!

Thank you a lot for your reply, I also tried something similar myself earlier. 
The problem is it doesn't work. Copying your script also results in an empty fact table.

[chrismarlow]

Hi,

Oddly I didn't get an empty table.

My date settings (in the auto generated bit at the top of the script I have not posted) are DD/MM/YYYY, may be the issue if yours are different.

I will put up screenshots/QVW later.

Cheers,

Chris.

[de]

Yes, you are right!
The dates were the problem and your solution worked perfectly!
THANK YOU!

[chrismarlow]

Dates in QV can be a bit painful (in my opinion).

I did have a look at interval match as well  - need to be careful with the ranges - but below should basically do the same;

fact:
LOAD * INLINE [
    OrderDate, TerritoryID
    01/01/2019, 1
    01/02/2019, 1
    01/01/2019, 2
];

dim:
LOAD * INLINE [
 TerritoryID, EmployeeID, StartDate, EndDate
	1, 1, 15/12/2018, 14/01/2019
	1, 2, 15/01/2019, 14/02/2019
	2, 3, 15/12/2018, 14/01/2019
];

interval_match:
NoConcatenate
IntervalMatch( OrderDate, TerritoryID)
Load 
	StartDate, 
	EndDate,
	TerritoryID
Resident dim;

left join (interval_match)
Load
	*
Resident dim;

DROP Tables fact, dim;