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.

maayanhad
New Contributor

Join 2 tables with a condition

Dear Community,

I have 2 tables and that  I want to connect them with a condition And create a new field

For example:

Table A:

table a.PNG      

Table B:

table b.PNG

When the USERID is the same and ACTIVITYDATE in Table A is between TimeStart And TimeEnd in Table B Create a new field in

Table A with the matching TimeStart


Like this:

table c.PNG
Is it possible to do this?

If so then I would be happy to help

1 Solution

Accepted Solutions

Re: Join 2 tables with a condition

Check this

TableA:

LOAD USERID,

Date(Date#(ACTIVITYDATE, 'DD-MM-YYYY hh:mm:ss'), 'DD-MM-YYYY hh:mm:ss') as ACTIVITYDATE;

LOAD * INLINE [

    USERID, ACTIVITYDATE

    A, 15-05-15 01:58:49

    B, 15-05-15 01:59:06

    A, 17-05-15 21:27:12

    A, 17-05-15 21:33:34

    B, 17-05-15 21:33:46

    C, 17-05-15 21:37:21

];

TableB:

LOAD USERID,

Date(Date#(TimeStart, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm') as TimeStart,

Date(Date#(TimeEnd, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm') as TimeEnd;

LOAD * INLINE [

    USERID, TimeStart, TimeEnd

    A, 14/05/2015 16:55, 15/05/2015 02:34

    B, 17/05/2015 16:34, 18/05/2015 02:47

    A, 17/05/2015 20:30, 18/05/2015 01:00

];

Left Join (TableA)

IntervalMatch(ACTIVITYDATE, USERID)

LOAD TimeStart,

TimeEnd,

USERID

Resident TableB;

Left Join (TableA)

LOAD *

Resident TableB;

DROP Table TableB;

Capture.PNG

5 Replies
maayanhad
New Contributor

Re: Join 2 tables with a condition

hi Sunny,

Thanks for the Reply.

I saw the IntervalMatch way but I do not know how to do it with 2 condition's first the USERID and second the date

Re: Join 2 tables with a condition

This is called Extended interval match where you join not just on the date range, but also another common field or fields.... Look at the attached document to see how this can be solved in different ways. The document is from this thread

IntervalMatch and Slowly Changing Dimensions

Re: Join 2 tables with a condition

Check this

TableA:

LOAD USERID,

Date(Date#(ACTIVITYDATE, 'DD-MM-YYYY hh:mm:ss'), 'DD-MM-YYYY hh:mm:ss') as ACTIVITYDATE;

LOAD * INLINE [

    USERID, ACTIVITYDATE

    A, 15-05-15 01:58:49

    B, 15-05-15 01:59:06

    A, 17-05-15 21:27:12

    A, 17-05-15 21:33:34

    B, 17-05-15 21:33:46

    C, 17-05-15 21:37:21

];

TableB:

LOAD USERID,

Date(Date#(TimeStart, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm') as TimeStart,

Date(Date#(TimeEnd, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm') as TimeEnd;

LOAD * INLINE [

    USERID, TimeStart, TimeEnd

    A, 14/05/2015 16:55, 15/05/2015 02:34

    B, 17/05/2015 16:34, 18/05/2015 02:47

    A, 17/05/2015 20:30, 18/05/2015 01:00

];

Left Join (TableA)

IntervalMatch(ACTIVITYDATE, USERID)

LOAD TimeStart,

TimeEnd,

USERID

Resident TableB;

Left Join (TableA)

LOAD *

Resident TableB;

DROP Table TableB;

Capture.PNG

maayanhad
New Contributor

Re: Join 2 tables with a condition

It's work!

thank you!

And thank you for the document

Community Browser