Hello gurus!
I've got some time series data of meter reading values. The only problem is that the source system doesn't always give you readings you might expect. It is 'designed' to give daily values of a meter reading, but that doesn't mean you'll alwyas get one row per day, instead you might get something that looks like the following:
Consumption Date Reading
| 2015-10-29 00:00:00.0 | 4491 |
| 2015-10-30 00:00:00.0 | 4516 |
| 2015-10-31 00:00:00.0 | 4537 |
| 2015-11-01 00:00:00.0 | 4543 |
| 2015-11-01 00:00:00.0 | 4551 |
| 2015-11-02 00:00:00.0 | 4557 |
| 2015-11-04 00:00:00.0 | 4561 |
| 2015-11-04 00:00:00.0 | 4568 |
| 2015-11-05 00:00:00.0 | 4572 |
| 2015-11-07 00:00:00.0 | 4578 |
| 2015-11-08 00:00:00.0 | 4595 |
| 2015-11-08 00:00:00.0 | 4597 |
| 2015-11-10 00:00:00.0 | 4604 |
| 2015-11-11 00:00:00.0 | 4608 |
| 2015-11-12 00:00:00.0 | 4612 |
| 2015-11-13 00:00:00.0 | 4616 |
| 2015-11-14 00:00:00.0 | 4620 |
So, everything is golden on 10.29,10.30,10.31, but then I get two reading values on 11.1, no reading for 11.3, no reading on 11.6, and again double readings on 11.8 and no reading on 11.9. Calculating a sum of consumption across a large timeframe would still give me a real value, but my ultimate BI solution wants to tie this data together at a *daily level* to things like weather and/or customer events. For that to work, I need one day for 11.1 / 11.8 instead of two, and one row for 11.3, 11.6, 11.9 instead of zero. Ideally I'd *also* find a way to split up my readings so that I can give an average consumption for the double days and missing days; i.e., just creating a row and giving it 0 consumption would make my output look awful.
I've been fighting with the multi-row tool a lot trying to figure this out and have solved one of the permutations, but was hoping that someone out there had a better way to slog through it.
Thanks!
brian
