topic Re: Time split in QlikView

Time split

Wed, 19 Sep 2012 15:54:49 GMT

I have me record set which contains a start and end datetime field. I can capture the number of minutes between these 2 fields. What i need to do is split the total minutes down over a 24 hour period so that i can see how many of those minutes were between 10-11, 11-12, 12-13 etc...

After extensively searching the community pages and trying different things i've come to the conclusion that i would perhaps best achieve this by using a LOOP to generate the 24 hour time periods for each row into a new table nad split the number of minutes across each time band. I'll also need to store the ROW_ID for each record into this table. i'm pretty sure this will give me what i need but i don't really know where to begin on this one...

Is anyone able to help to get me moving in the right direction

Thanks

Lewis

Note: http://community.qlik.com/message/60017#60017 i've seen this post so i'm looking at using this method to see if i can get what i want

Re: Time split

Wed, 19 Sep 2012 17:46:48 GMT

For anyone interested this script did what i wanted.

Stationary_time_split:

load

RowID,

hour(time(HourStart, 'hh:mm')) as HourID,

time(HourStart, 'hh:mm') as HourStart,

time(HourEnd, 'hh:mm') as HourEnd,

num#(interval(time(rangemin([Vacated Time], HourEnd), 'hh:mm') - time(rangemax([Arrived Time], HourStart), 'hh:mm') , 'mm') ) as ActiveTime

;

load

floor([Arrived Time], 1/24) + (Iterno() - 1)/24 as HourStart,

floor([Arrived Time], 1/24) + (Iterno())/24 as HourEnd

resident Bay_Sensor_activity

while

floor([Arrived Time], 1/24) + Iterno()/24 <= ceil([Vacated Time], 1/24) + 0.02 //

;

This broke my time based data into hourly segments based on the start and end time. It calculates the time between each hour period so that i can see the profile of time being taken over the respective hours.

Many thanks to Oleg Troyansky for providing the answer in the previous post that i have adapted!

Re: Time split

Wed, 09 Oct 2013 10:50:52 GMT

I actually have kind of the same problem.

I tried your solution and it works great. However, I also need to include the date aswell. I have have a list of shifts that have a start date with time and and end date with time. So I also need to know the day of the interval, not just the hour.

For example, if I have a shift that goes on for 2 days, I'm gonna have 2 slots for the same time stamp, lets say '03:00-04:00', without knowing which day is what.

Does anybody know a solution for this?