IntervalMatch on Time field

[regowins]

Hi -  I am not sure why I cant do an interval match on a time field. I have an arrival time & depart time and want to match on 24  1 hr events. I have attached my sample QV doc. Any help would be great. Thanks!

[MK_QSL]

I can see that your Time is having one hour interval while your Arrival and Depart Time is having more than one hour.

How can you match them?

[regowins]

Maybe I misunderstood intervalmatch. I thought that my hour event for 10 am will fall under all intervals that have 10 am in them, for example, 9:45 - 11 am or 9 - 12 pm...etc

[MK_QSL]

Intervalmatch will not help you in this case. You need to run a loop from Arival to Depart hour...

[regowins]

OK, can you give me a quick example? Thanks!

[regowins]

So here is another test I did which works exactly the way I want. BTW, the 'Staying Chart' in this app is what I am trying to accomplish in the original app. In this app I have one hour intervals and more than one hour begin and end times. Why is it that this app works but my original does not?  Thanks!!

[MK_QSL · Accepted solution]

Temp_Events:

LOAD * Inline [

TIME,ZID

12:00:00 AM,1

01:00:00 AM,2

02:00:00 AM,3

03:00:00 AM,4

04:00:00 AM,5

05:00:00 AM,6

06:00:00 AM,7

07:00:00 AM,8

08:00:00 AM,9

09:00:00 AM,10

10:00:00 AM,11

11:00:00 AM,12

12:00:00 PM,13

01:00:00 PM,14

02:00:00 PM,15

03:00:00 PM,16

04:00:00 PM,17

05:00:00 PM,18

06:00:00 PM,19

07:00:00 PM,20

08:00:00 PM,21

09:00:00 PM,22

10:00:00 PM,23

11:00:00 PM,24

];

Events:

LOAD

  ZID,

  Time(TIME,'hh:mm:ss') as Time

Resident Temp_Events;

DROP Table Temp_Events;

Intervals:

LOAD

  %DLRO_KEY,

  Type,

  Arrival_Datetime,

  Time(Frac(Arrival_Datetime),'hh:mm:ss') as Arrival_Time,

  Depart_Datetime,

  Time(Frac(Depart_Datetime),'hh:mm:ss') as Depart_Time,

  Occupancy_Hour,

  Visits

FROM

[..\Desktop\TB04_20150627_222530.xls]

(biff, embedded labels, table is Sheet1$);

Join

IntervalMatch(Time)

LOAD

  Arrival_Time,

  Depart_Time

Resident Intervals;

[regowins]

Great ! This worked the way I wanted but I then found out that with a time stamp some events could fall outside of the interval range. For example, event  of 11 am would fall outside of interval 11:05 am - 2:00 pm. So I changed the interval range to the hour ie. 8 - 10 am that picked up all my values.  

So does this mean that interval match does not work well with Timestamps and I have to convert using Frac() function?

Anyway, Thanks for the help!!