Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 tables which I want to join in the correct way ... sounds simple, but it's a head breaker for me at the moment.
Call:
LOAD Call_id
,Employee
,Call_date
FROM Call;
NAV_Call:
LOAD Nav_Callnr
,Employee
,start_datetime
,end_datetime
FROM NAV_Call;
The resulting table should be:
JoinedCall:
LOAD Call_id
,Employee
,Call_date
,Nav_Callnr
,start_datetime
,end_datetime
RESIDENT ...
The difficulty is that the Call_date should be between start_datetime and end_datetime of the other table.
But also take into account that the Employee should be the same!!!
There are options to join both tables with a simple join and than filtering out the 'wrong' joined items.
But this option is time-consuming and not suitable for our amount of data.
After that I first thought of using the IntervalMatch on the date fields, but then I don't have the check on the Employee ...
INNER JOIN (Call)
IntervalMatch (Call_date)
LOAD end_datetime
,start_datetime
RESIDENT NAV_Call;
LEFT JOIN (Call)
LOAD * RESIDENT NAV_Call;
sample with data:
Call
Call_id | Employee | Call_date |
---|---|---|
1200 | AFU | 17-5-2015 10:34:12 |
1201 | IWE | 17-5-2015 10:35:56 |
1202 | MEN | 17-5-2015 10:36:03 |
NAV_Call
Nav_Callnr | Employee | start_datetime | end_datetime |
---|---|---|---|
5698 | AFU | 17-5-2015 10:33:24 | 17-5-2015 10:35:58 |
5699 | PBR | 17-5-2015 10:33:56 | 17-5-2015 10:34:06 |
5700 | MEN | 17-5-2015 10:35:02 | 17-5-2015 10:35:57 |
5701 | IWE | 17-5-2015 10:35:25 | 17-5-2015 10:35:59 |
5702 | MEN | 17-5-2015 10:35:59 | 17-5-2015 10:36:10 |
5703 | AFU | 17-5-2015 10:36:02 | 17-5-2015 10:36:25 |
the join should result in:
JoinedCall
Call_id | Employee | Call_date | Nav_callnr | start_datetime | end_datetime |
---|---|---|---|---|---|
1200 | AFU | 17-5-2015 10:34:12 | 5698 | 17-5-2015 10:33:24 | 17-5-2015 10:35:58 |
1201 | IWE | 17-5-2015 10:35:56 | 5701 | 17-5-2015 10:35:25 | 17-5-2015 10:35:59 |
1202 | MEN | 17-5-2015 10:36:03 | 5702 | 17-5-2015 10:35:59 | 17-5-2015 10:36:10 |
Has anyone got idea's of how to solve this??
Regards, Anita
Intervalmatch also has an extended syntax where you can add the key fields so you can match the intervals per employee.
IntervalMatch (Call_date, Employee)
LOAD end_datetime
,start_datetime
,Employee
RESIDENT NAV_Call;
Intervalmatch also has an extended syntax where you can add the key fields so you can match the intervals per employee.
IntervalMatch (Call_date, Employee)
LOAD end_datetime
,start_datetime
,Employee
RESIDENT NAV_Call;
Hi,
one minor change regarding the order of the start and end timestamps:
IntervalMatch (Call_date, Employee)
LOAD start_datetime
,end_datetime
,Employee
RESIDENT NAV_Call;
hope this helps
regards
Marco
it's correct the datetime normally should be start, end ... but in my case I need it exactly the opposite 😉