Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings.
I am trying to create intervals to cover the entire day (00:00 - 23:59) based on the input table. My input table is as follows:
Category | Time | Value |
A | 10:15 | 15 |
A | 16:29 | 25 |
A | 18:00 | 30 |
B | 12:00 | 29 |
Required output table
Category | Time Start | Time End | Avg Value Calculation |
A | 00:00 | 09:59 | AVG(15,25,30) |
A | 10:00 | 10:29 | AVG(15) |
A | 10:30 | 15:59 | AVG(15,25,30) |
A | 16:00 | 16:29 | AVG(25) |
A | 16:30 | 17:59 | AVG(15,25,30) |
A | 18:00 | 18:29 | AVG(30) |
A | 18:30 | 23:59 | AVG(15,25,30) |
B | 00:00 | 11:59 | AVG(29) |
B | 12:00 | 12:29 | AVG(29) |
B | 12:30 | 23:59 | AVG(29) |
Appreciate any assistance.
Is not clear how you get from your data to the required output. For example the first three rows look inconsistent with interval mapping the input. And how are the intervals determined?
Hi Jontydkpi
Category A has values at 3 times 10:15, 16:29, 18:00.
I would like to create time bands for Category A to cover the entire day up to half hour interval.
For Time 10:15,
time band is 1000 - 1029.,
The previous time band will be 0000 - 0959
Next time band will be 1030 - 1559.
For time 16:29, time band is 1600 - 1629.
next time band is 1630 - 1759.
For 18:00, time band is 1800 - 1829.
As last value is 1800, the next time band will go upto end of day i.e. 1830 - 2359.
Hope this makes sense
So you mean create a time band of 30 minutes for each time in your source?
Now please explain the average calc for A.
Yes.
For Time that exists, the time band will be 30 mins. I also need time bands that will cover the entire 24 hours. So for Category A, the immediate time bands will be 1000 - 1029 (10:15), 1600 - 1629 (16:29) and 1800 - 1829 (18:00). In addition, I need to create time bands 0000 - 0959, 1030 - 1559, 1630 - 1759 and 1830 - 2359. All these 7 time bands cover the entire day.
The average calculation is not an issue. When the Time is present then the average will be value in that time band. Eg: for 1000 - 1029, it will be based on value 15. For time bands that do not exist, it will be based on Average of the Category (in this case A) i.e. AVG(15,25,30).
Primarily i cant figure out how to get the time bands to cover 24 hours.