topic Re: IntervalMatch with a JOIN in QlikView

IntervalMatch with a JOIN

de — Sat, 16 Nov 2024 03:18:45 GMT

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!

Re: IntervalMatch with a JOIN

chrismarlow — Thu, 13 Jun 2019 19:12:06 GMT

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;

Re: IntervalMatch with a JOIN

de — Fri, 14 Jun 2019 06:46:20 GMT

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.

Re: IntervalMatch with a JOIN

chrismarlow — Fri, 14 Jun 2019 07:21:40 GMT

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.

Re: IntervalMatch with a JOIN

de — Fri, 14 Jun 2019 08:25:29 GMT

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

Re: IntervalMatch with a JOIN

chrismarlow — Fri, 14 Jun 2019 20:51:29 GMT

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;