Join 2 tables with a condition

[Maayan]

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 B:

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:

Is it possible to do this?

If so then I would be happy to help

[sunny_talwar]

Check these

IntervalMatch (Extended Syntax) ‒ QlikView‌

IntervalMatch and Slowly Changing Dimensions

Need an example of extended interval match

[Maayan]

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 · Accepted solution]

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;

[Maayan]

It's work!

thank you!

And thank you for the document