2 Replies Latest reply: Oct 9, 2013 6:50 AM by Kim Lindell RSS

    Time split

      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

          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

              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?