Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
What does your source data look like?
-Rob
Looks quite similar to a question Henric answered just recently:
http://community.qlik.com/message/259927#259927
Hope this helps,
Stefan
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
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.
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 //
;
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.
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.
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;
Dear experts,
I have a similar problem.
Maybe someone has an idea?
http://community.qlik.com/thread/67078
Thx
chesterluck