date range join

[#1]

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.

[sunny_talwar · Accepted solution]

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,

  ID

Resident Directory;

Left Join (Exercise)

LOAD *

Resident Directory;

DROP Table Directory;

[#3]

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