Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
de
New Contributor II

IntervalMatch with a JOIN

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!


 

Labels (5)
1 Solution

Accepted Solutions
chrismarlow
Valued Contributor

Re: IntervalMatch with a JOIN

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;
5 Replies
chrismarlow
Valued Contributor

Re: IntervalMatch with a JOIN

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
New Contributor II

Re: IntervalMatch with a JOIN

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
Valued Contributor

Re: IntervalMatch with a JOIN

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
New Contributor II

Re: IntervalMatch with a JOIN

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

chrismarlow
Valued Contributor

Re: IntervalMatch with a JOIN

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;