topic Join 2 tables with a condition in QlikView

Join 2 tables with a condition

Maayan — Mon, 18 Dec 2017 09:23:17 GMT

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

Re: Join 2 tables with a condition

sunny_talwar — Mon, 18 Dec 2017 13:27:15 GMT

Check these

IntervalMatch (Extended Syntax) ‒ QlikView‌

IntervalMatch and Slowly Changing Dimensions

Need an example of extended interval match

Re: Join 2 tables with a condition

Maayan — Mon, 18 Dec 2017 14:09:20 GMT

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

sunny_talwar — Mon, 18 Dec 2017 16:54:51 GMT

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

sunny_talwar — Tue, 19 Dec 2017 02:06:53 GMT

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;

Re: Join 2 tables with a condition

Maayan — Tue, 19 Dec 2017 08:10:06 GMT

It's work!

thank you!

And thank you for the document