topic Re: IntervalMatch on Time field in QlikView

IntervalMatch on Time field

regowins — Sat, 27 Jun 2015 20:51:46 GMT

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!

Re: IntervalMatch on Time field

MK_QSL — Sat, 27 Jun 2015 21:29:29 GMT

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?

Re: IntervalMatch on Time field

regowins — Sat, 27 Jun 2015 21:59:57 GMT

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

Re: IntervalMatch on Time field

MK_QSL — Sat, 27 Jun 2015 22:11:54 GMT

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

Re: IntervalMatch on Time field

regowins — Sun, 28 Jun 2015 01:10:36 GMT

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

Re: IntervalMatch on Time field

regowins — Sun, 28 Jun 2015 02:06:28 GMT

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!!

Re: IntervalMatch on Time field

MK_QSL — Sun, 28 Jun 2015 06:23:48 GMT

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;

Re: IntervalMatch on Time field

regowins — Tue, 30 Jun 2015 02:05:39 GMT

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!!