Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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?
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
Intervalmatch will not help you in this case. You need to run a loop from Arival to Depart hour...
OK, can you give me a quick example? Thanks!
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!!
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;
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!!