Class function with time interval

[#1]

Hello All,

I am pretty new with qlikview and I need your help on solving my problem.

My data contains two separate time (Status_U and Status_F) where status U is when the vehicle got their assignment and status F is when the vehicle has arrived to their assignment and one column of area.

I want to create class interval of waiting time (Status_F - Status_U) with interval of <10 minutes, <20 minutes, 10-15 minutes, 15-30 minutes, 30-45 minutes, 45-60 minutes and >60 minutes. I tried to use class function but it didn't work and I am really confuse.

Another problem is that I am using binary code to read from another qlikview file. Therefore I can't change anything from the database (excel file).

Any suggestions and help are well appreciated.

Thank you in advance

Arini

[#2]

Hi Arini,

what you need is "IntervalMatch".

See the attached example.

Good luck!

Rainer

[#3]

Hey Rainer,

Thank you for your suggestion. I tried using the interval match as you suggested but it's a little bit different from what i want.

The problem is that my data contains timestamp with hh:mm:ss and I want to classify it with different class interval ex: 5 minutes then 15 minutes.

I tried to use

=dual(time(floor(VänteTid, 1/96),'hh:mm:ss') & ' - ' &

time(ceil (VänteTid, 1/96),'hh:mm:ss')

,floor(frac(VänteTid),1/96))

From one of the forum solution but it doesnt really get what i want. This is only for class interval 15 minutes.

And i tried to write

Load
If(VänteTid <= 00:10:00, 'Under 10 min'
If(VänteTid >=00:10:00 and VänteTid <= 00:15:00, '10-15 Min',
If(VänteTid > 00:15:00 and VänteTid <= 00:30:00, '15-30 Min',
If(VänteTid > 00:30:00 and VänteTid <= 00:45:00, '30-45 Min',
If(VänteTid > 00:45:00 and VänteTid <= 00:60:00, '45-60 Min'',
If(VänteTid > 00:60:00, 'Över 60 Min')))) as TidInterval
Resident *;

in the script but i didnt work beacuse it didnt recognise the timestamp. So I'm really confuse.

Thank you

[#4]

Hi,

You can try something like the below script...

LOAD Vehicle,
time(Status_U) as Status_U,
time(Status_F) as Status_F,
hour(time(Status_F)-time(Status_U))*60+
minute(time(Status_F)-time(Status_U)) as TimeDiff_minute
FROM
hour.xls
(biff, embedded labels, table is Sheet1$);

TimeRange:
Load * inline
[
start,end,Minute_Range
0,10,Under 10 min
10.01,15,10-15 Min
15.01,30,15-30 Min
30.01,45,30-45 Min
45.01,60,45-60 Min
60.01,999,Over 60 Min
];

intervalmatch(TimeDiff_minute)
Load
start,end
resident TimeRange;

Regards,

Silvia

[#5]

Hi,

Thanks for your help but unfortunately it still didnt work. I attached a little example of my qlikview.

Thank you

[johnw · Accepted solution]

Untested, but just use this as an additional field in the same load where you load VänteTid:

If(VänteTid < time#(10,'mm'), dual('Under 10 min', 1),
If(VänteTid <= time#(15,'mm'), dual('10-15 Min' ,10),
If(VänteTid <= time#(30,'mm'), dual('15-30 Min' ,15),
If(VänteTid <= time#(45,'mm'), dual('30-45 Min' ,30),
If(VänteTid <= time#(60,'mm'), dual('45-60 Min'' ,45),
dual('Över 60 Min' ,60)))))) as TidInterval

Basically, I think you were on the right path, but QlikView couldn't recognize 00:15:00 as a time without you telling it explicitly that it was a time. That's what I'm doing with the time#() function. The dual() function is to help with sorting, allowing you to sort the TidInterval numerically. An intervalmatch would probably be more efficient from a load time standpoint, but I think less clear from a maintenance standpoint, particularly with inexperienced developers.

[#7]

Hi John,

I was having dificulties on how qlikview could recognize the time.

Your solution works well and I learn something new. Thank you very much. 🙂

Arini

[#8]

Hi John,

I have some charts with time intervals calculated as you suggested in this post, but I have a requirement to always show all interval values even if the expression total for that interval is zero.

When using a calculated dimension, we don't have the option to "Show all values" and I cannot calculate my dimension in script and use LOAD * INLINE with intervalmatch because my time dimension is a difference between two dates from different tables/sources.

Do you have any suggestion to answer this requirement?

Thank you so much in advance,