Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

afuchten
Not applicable

Join tables, but with a timestamp between ... a head breaker!

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_idEmployeeCall_date
1200AFU17-5-2015 10:34:12
1201IWE17-5-2015 10:35:56
1202MEN17-5-2015 10:36:03

NAV_Call

Nav_CallnrEmployeestart_datetimeend_datetime
5698AFU17-5-2015 10:33:2417-5-2015 10:35:58
5699PBR17-5-2015 10:33:5617-5-2015 10:34:06
5700MEN17-5-2015 10:35:0217-5-2015 10:35:57
5701IWE17-5-2015 10:35:2517-5-2015 10:35:59
5702MEN17-5-2015 10:35:5917-5-2015 10:36:10
5703AFU17-5-2015 10:36:0217-5-2015 10:36:25

the join should result in:

JoinedCall

Call_idEmployeeCall_dateNav_callnrstart_datetimeend_datetime
1200AFU17-5-2015 10:34:12569817-5-2015 10:33:2417-5-2015 10:35:58
1201IWE17-5-2015 10:35:56570117-5-2015 10:35:2517-5-2015 10:35:59
1202MEN17-5-2015 10:36:03570217-5-2015 10:35:5917-5-2015 10:36:10

Has anyone got idea's of how to solve this??

Regards, Anita

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Not applicable

Re: Join tables, but with a timestamp between ... a head breaker!

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;  

 


talk is cheap, supply exceeds demand
3 Replies
Gysbert_Wassenaar
Not applicable

Re: Join tables, but with a timestamp between ... a head breaker!

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;  

 


talk is cheap, supply exceeds demand
MarcoWedel
Not applicable

Re: Join tables, but with a timestamp between ... a head breaker!

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

afuchten
Not applicable

Re: Join tables, but with a timestamp between ... a head breaker!

it's correct the datetime normally should be start, end ... but in my case I need it exactly the opposite ;-)