Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Splitting time

Hi all

I'm currently using the loa/d script below in order to create a profile of time from a start and end time.  The script works brilliantly when over a single days period.

Stationary_time_split:

load

          RowID,

          [Arrived Date] as Time_split_date,

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

;

What i'm struggling with is if the end time is on the following day. 

Currently if the date is 26/09/2012 and i have a start time of 12:33 and an end time of 14:47 then i get the following

Start      End       ActiveTime

12:00     13:00     27

13:00     14:00     60

14:00     15:00     47

What I want is if i have a start time of 22:33 and an end time of 01:47, the following day, then get the following

Date              Start      End       ActiveTime

26/09/2012     22:00     23:00     27

26/09/2012     23:00     00:00     60

27/09/2012     00:00     01:00     60

27/09/2012     01:00     02:00     47

I think i have to amend the WHILE stagement but i've not been able to get it to work - i'm thinking of it using an if or peek but i'm not sure...

anyone got any ideas

Lewis

1 Solution

Accepted Solutions
Not applicable
Author

With a bit of messing about i got what i needed.  The finished script is as follows

Stationary_time_split:

LOAD BayID,

          BayNO,

          RowNo() as RowID,

          Start,

          StartDate,

          Finish,

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

          num#(interval(time(rangemin(Vacated, Finish), 'hh:mm') - time(rangemax(Arrival, Start), 'hh:mm') , 'mm') ) as ActiveTime

          ;

Load

          BayID,

          BayNO,

          Vacated,

          Arrival,

          Time(Floor(Arrival,1/24)+(iterno()-1)/24,'hh:mm') as Start,

          Date(Floor(Arrival,1/24)+(iterno()-1)/24,'dd/mm/yyyy') as StartDate,

          Time(Floor(Arrival,1/24)+iterno()/24,'hh:mm') as Finish

      While Arrival+(iterno()-1)/24 <= ceil(Finish, 1/24);// + 0.02;

Load BayID,

          Vacated,

          Arrival,

          Bay_No as BayNO,

   If(Vacated<Arrival,Vacated+1,Vacated) as Finish

   resident Bay_Sensor_activity;

View solution in original post

9 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

What does your source data look like?

-Rob

swuehl
MVP
MVP

Looks quite similar to a question Henric answered just recently:

http://community.qlik.com/message/259927#259927

Hope this helps,

Stefan

Not applicable
Author

Stefan

I've just had a look at the other post and it pretty much does what my script does.  I suspect the insperation for that script started where mine did which was a post from Oleg (http://community.qlik.com/message/60017#60017).  It still doesn't quite do what i need though.

Rob,

The data that i have is individual rows which is data on vehicle arivals and vacations from a parking spot.  For the vast majority of instances the vehicle movements are all on the same day and the script works fine.  I you see the image below some vehicles leave the following day.  The script is working with min and max time values and is expecting the min and max in a logical order, i.e. the start time is always less that the vacated time.  Tis is getting thrown when the vehicle arives late at night and leaves the following morning.

sensor data.PNG

Regards

Lewis

swuehl
MVP
MVP

Just to double check, have you tried this line from Henric's post (in the load that will feed the load with while loop)?

   If(Finish<Start,Finish+1,Finish) as Finish

edit: if your time field is actually a datetime field, you don't need to add 1, just make sure that you don't strip of the date (integer part) in your while loop.

Not applicable
Author

Stefano

I've added the IF statement to the first load statement but looking at it all it is going to do is if the finish time is less than the start then just add an hour to the finish time.

so if me start is 22:35 and my finish is the following morning at 02:37 then its just added an extra hour onto the finish time so that becomes 03:37 which is still less and then actually justs adds an extra hour to my time profile

Lewis

Stationary_time_split:

load

          RowID,

          Finish,

          [Arrived Date] as Time_split_date,

          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

          *,

          If([Vacated Time]<[Arrived Time],[Vacated Time]+1,[Vacated Time]) as Finish,

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

;

swuehl
MVP
MVP

Time is stored as fraction of a day within QV, so 12:00 would be 0.5, 18:00 is 0.75

The integer part is giving you the date (starting from Dec 30,1899).

So adding 1 to a value that is representing DATETIME in QV adds a day, not an hour.

Not applicable
Author

Right OK, i'd been working with just the time element (which is why i thought it was just going to add an hour) in the script and not the datetime which i do have.  i'll amend and see if that then gives me what i need.

Not applicable
Author

With a bit of messing about i got what i needed.  The finished script is as follows

Stationary_time_split:

LOAD BayID,

          BayNO,

          RowNo() as RowID,

          Start,

          StartDate,

          Finish,

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

          num#(interval(time(rangemin(Vacated, Finish), 'hh:mm') - time(rangemax(Arrival, Start), 'hh:mm') , 'mm') ) as ActiveTime

          ;

Load

          BayID,

          BayNO,

          Vacated,

          Arrival,

          Time(Floor(Arrival,1/24)+(iterno()-1)/24,'hh:mm') as Start,

          Date(Floor(Arrival,1/24)+(iterno()-1)/24,'dd/mm/yyyy') as StartDate,

          Time(Floor(Arrival,1/24)+iterno()/24,'hh:mm') as Finish

      While Arrival+(iterno()-1)/24 <= ceil(Finish, 1/24);// + 0.02;

Load BayID,

          Vacated,

          Arrival,

          Bay_No as BayNO,

   If(Vacated<Arrival,Vacated+1,Vacated) as Finish

   resident Bay_Sensor_activity;

Anonymous
Not applicable
Author

Dear experts,

I have a similar problem.

Maybe someone has an idea?

http://community.qlik.com/thread/67078

Thx

chesterluck