Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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
Highlighted
Specialist
Specialist

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;

View solution in original post

5 Replies
Highlighted
Specialist
Specialist

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;

View solution in original post

Highlighted
Contributor II
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.

 

 

Highlighted
Specialist
Specialist

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.

Highlighted
Contributor II
Contributor II

Re: IntervalMatch with a JOIN

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

Highlighted
Specialist
Specialist

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;