Time split

[#1]

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

[#2 · Accepted solution]

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!

[#3]

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?