Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
Exactly! I was close but didn't have my interval match correct. Thanks!!