topic date range join in QlikView

date range join

Wed, 25 Nov 2020 16:16:04 GMT

Question on best way to join 2 tables. I was looking into IntervalMatch but wasn't getting the results I wanted. Hoped this would be easy for one of you to tell me best way.

Exercise:
Load * Inline [
ID, ExerciseDate, Result
A, 12/18/2016, passed
B, 12/18/2016, failed
C, 12/18/2016, passed

...
];

Directory:
Load * Inline [
ID, StartDate, EndDate, Department
A, 6/1/2016, 7/28/2016, DeptA
A, 7/28/2016, 9/14/2016, DeptB
A, 9/14/2016, 12/16/2016, DeptC
A, 12/16/2016, 3/1/2016, DeptD
A, 3/1/2016, 12/31/9999, DeptE

...
];

I would want to see only the record reflecting DeptD as that was the department of the individual
when they took the exam.

Thanks.

Re: date range join

sunny_talwar — Thu, 22 Jun 2017 13:13:18 GMT

Is this what you want?

Try this

Exercise:

Load * Inline [

ID, ExerciseDate, Result

A, 12/18/2016, passed

B, 12/18/2016, failed

C, 12/18/2016, passed

];

Directory:

Load * Inline [

ID, StartDate, EndDate, Department

A, 6/1/2016, 7/28/2016, DeptA

A, 7/28/2016, 9/14/2016, DeptB

A, 9/14/2016, 12/16/2016, DeptC

A, 12/16/2016, 3/1/2016, DeptD

A, 3/1/2016, 12/31/9999, DeptE

];

Left Join (Exercise)

IntervalMatch(ExerciseDate, ID)

LOAD StartDate,

EndDate,

Resident Directory;

Left Join (Exercise)

LOAD *

Resident Directory;

DROP Table Directory;

Re: date range join

Thu, 22 Jun 2017 13:19:33 GMT

Exactly! I was close but didn't have my interval match correct. Thanks!!