topic Re: Splitting time in QlikView

Splitting time

Wed, 26 Sep 2012 17:03:40 GMT

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

Re: Splitting time

rwunderlich — Wed, 26 Sep 2012 17:55:45 GMT

What does your source data look like?

-Rob

Re: Splitting time

swuehl — Wed, 26 Sep 2012 19:53:06 GMT

Looks quite similar to a question Henric answered just recently:

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

Hope this helps,

Stefan

Re: Splitting time

Fri, 28 Sep 2012 08:45:02 GMT

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.

Regards

Lewis

Re: Splitting time

swuehl — Fri, 28 Sep 2012 08:56:52 GMT

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.

Re: Splitting time

Fri, 28 Sep 2012 09:49:16 GMT

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

;

Re: Splitting time

swuehl — Fri, 28 Sep 2012 10:04:53 GMT

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.

Re: Splitting time

Fri, 28 Sep 2012 10:32:33 GMT

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.

Re: Splitting time

Wed, 03 Oct 2012 18:39:50 GMT

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;

Re: Splitting time

Anonymous — Wed, 28 Nov 2012 09:39:11 GMT

Dear experts,

I have a similar problem.

Maybe someone has an idea?

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

Thx

chesterluck