topic Join tables where date between two dates in second table in QlikView

Join tables where date between two dates in second table

Wed, 20 Aug 2014 00:52:47 GMT

I have a table that contains events and a second table that contains locations. The events and location tables both contain a PersonID. The events table also contains a datetime of the event. The location table contains an arrive datetime and the depart datetime is derived based on the next location's datetime. As a result, the last location for a person is null. An event occurs at a location, but to determine the location of an event, it must be derived. The location of the event will be: events.PersonID = location.PersonID and events.datetime between location.arrivedatetime and location.departdatetime.

The tables below show sample table data.

Events Table

PersonID	Event	DateTime
1	x	8/1/2014 12:34:00 PM
1	y	8/1/2014 3:00:12 PM
1	z	8/1/2014 5:52:12 PM
2	x	8/1/2014 2:46:12 PM

Location Table

PersonID	Location	ArriveDateTime	DepartDateTime
1	Rm200	8/1/2014 12:15:09 PM	8/1/2014 5:09:34 PM
1	Rm201	8/1/2014 5:09:35 PM
2	Rm101	8/1/2014 2:09:14 PM

The desired end result is as below.

Events table

PersonID	Event	DateTime	Location
1	x	8/1/2014 12:34:00 PM	Rm200
1	y	8/1/2014 3:00:12 PM	Rm200
1	z	8/1/2014 5:52:12 PM	Rm201
2	x	8/1/2014 2:46:12 PM	Rm101

Any help in figuring this out is greatly appreciated. Thanks!

Re: Join tables where date between two dates in second table

rwunderlich — Wed, 20 Aug 2014 01:07:50 GMT

Take at look at "IntervalMatch" in the Ref Guide (Sections 27.3-4), specifically "Extended IntervalMatch". It should provide just what you are asking for.

Re: Join tables where date between two dates in second table

Wed, 20 Aug 2014 08:01:16 GMT

Hi Robert,

Use this Script for loading Data

Location:

LOAD PersonID,

Location,

ArriveDateTime,

DepartDateTime

FROM

[http://community.qlik.com/thread/130035]

(html, codepage is 1252, embedded labels, table is @2);

Event:

LOAD PersonID,

Event,

DateTime

FROM

[http://community.qlik.com/thread/130035]

(html, codepage is 1252, embedded labels, table is @1);

Inner Join

LOAD PersonID,

Location,

ArriveDateTime,

DepartDateTime

FROM

[http://community.qlik.com/thread/130035]

(html, codepage is 1252, embedded labels, table is @2);

Inner Join

IntervalMatch(DateTime,PersonID) Load ArriveDateTime,DepartDateTime,PersonID Resident Location;

//DROP Table Event;

DROP Table Location;

Your Desired output will be like this

I am also attaching sample Qvw

Regards,

Vivek

Re: Join tables where date between two dates in second table

Thu, 21 Aug 2014 02:59:11 GMT

Thank you both for the response. Inervalmatch was exactly what was needed, but I was not able to get the desired results until I saw how the inner joins was used in Vivek's response.

Re: Join tables where date between two dates in second table

Tue, 02 Feb 2016 09:42:46 GMT

Hi,

Is it possible to do something similar to this but according to user selection?

For Ex if the user selects a date it gets one location, and if another date is selected it returns another value?

Basically doing this type of search at runtime?

Thanks

Jon