Join tables where date between two dates in second table

[#1]

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!

[rwunderlich]

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.

[#3 · Accepted solution]

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

[#4]

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.

[#5]

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