Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Maayan
Contributor
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
sunny_talwar

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

View solution in original post

5 Replies
Maayan
Contributor
Contributor
Author

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

sunny_talwar

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

sunny_talwar

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

Maayan
Contributor
Contributor
Author

It's work!

thank you!

And thank you for the document