Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI,
I have this data to read in QV:
Customer | Date | From | To |
A | 02/01/2010 | 6:30 | 8:30 |
B | 02/01/2010 | 7:00 | 8:00 |
C | 02/01/2010 | 7:00 | 7:30 |
D | 02/01/2010 | 8:00 | 10:30 |
E | 02/01/2010 | 8:00 | 8:15 |
F | 02/01/2010 | 8:00 | 9:15 |
G | 02/01/2010 | 10:00 | 12:00 |
and i want to have a chart like this :
From - To | # Customers |
00:00 - 00:59 | 0 |
01:00 - 01:59 | 0 |
02:00 - 02:59 | 0 |
03:00 - 03:59 | 0 |
04:00 - 04:59 | 0 |
05:00 - 05:59 | 0 |
06:00 - 06:59 | 0,5 |
07:00 - 07:59 | 2,5 |
08:00 - 08:59 | 2,75 |
09:00 - 09:59 | 1,25 |
10:00 - 10:59 | 1,5 |
11:00 - 11:59 | 1 |
12:00 - 12:59 | 0 |
where each customer that is online a whole hour = 1
half hour = 0,5
What to do in the script for this ?? Is there a easy way ??
Thanks Dirk.
Dirk,
if your data volume allows, you can generate hourly rows for each full or partial hour when the customer was active (based on a From-To range), and calculate the portion of the hour when the customer was online. See attached example.
best regards,
Dirk,
take a look at 'intervalmatch' which should give you some ideas.
Regards,
Gordon
Gordon,
Don't think this will work. Intervalmatch is for 1 value between a min and max value.
In this case, i have to split every time into hour parts.
Dirk
Dirk,
if your data volume allows, you can generate hourly rows for each full or partial hour when the customer was active (based on a From-To range), and calculate the portion of the hour when the customer was online. See attached example.
best regards,
Oleg
I used your example to split the time into the minutes between each hour segment as such
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 //
;
What i am struggling with though is if the TO date is different to the FROM date, i.e the time period extends past midnight into the following day. I'm trying to amend the above to take account of the day and get it to generate the time through to the following day - however i'm struggling. Any help on this would be very much appreciated
Thanks
Lewis