Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am building a dashboard to calculate MPG for trucks.
The data comes in bursts and not linear in nature.
I have attached the QVD file and need to manipulate it within the load script.
fields include MPG, miles, consumed total, drive time and Driver code. As well as a startdate and an EndDate field.
What i would like to do is make the data continuous.
what i have now:
Start Date - 10/19/09
EndDate- 10/26/09
MPG- 9.526
Driver- PATAN
WHAT I NEED (from above):
date- 10/19/09 MPG- 9.526 Driver-PATAN
date- 10/20/09 MPG- 9.526 Driver-PATAN
date- 10/21/09 MPG- 9.526 Driver-PATAN.... Until 10/26/09
Then @ 10/27/09 When a new period starts:
10/27/09 MPG-10.5 Driver-PATAN,.....
Basically i just need to fill in space in between ranges with the same info for every day.
What do you suggest?
Here is a sample of the QVD
%UnitDate | MPGUnitNo | MPGDriverCode | MPGStartDate | MPGEndDate | MPGWkStart | MPGWkEnd | MPGOverRevTime | MPGTripCount | MPGTotalDistance | MPGDrivingTime | MPGEngineTime | MPGPTOFuelConsumed | MPGFuelConsumed | MPGParkedIdleTime | MPGShortIdle | MPGInterTripIdle | MPGMovingTime | MPGIdleFuelGallons |
REEF VAN 639987 | REEF VAN 6 | MORMAR | 5/25/2009 | 6/23/2009 | 6/22/2009 | 6/29/2009 | 1 | 2 | 4 | 43 | 202 | 0 | 3 | 2 | 13 | 143 | 36 | 19 |
REEF VAN 940004 | REEF VAN 9 | 614285288 | 5/16/2009 | 7/10/2009 | 7/6/2009 | 7/13/2009 | 12 | 2 | 160 | 214 | 237 | 0 | 21 | 0 | 15 | 15 | 204 | 2 |
TEST CART39948 | TEST CART | WIGGCH | 4/16/2009 | 5/15/2009 | 5/11/2009 | 5/18/2009 | 99 | 16 | 1559 | 1784 | 1821 | 0 | 260 | 0 | 12 | 12 | 1723 | 14 |
1040032 | 10 | BURERD | 8/5/2009 | 8/7/2009 | 8/3/2009 | 8/10/2009 | 0 | 6 | 543 | 702 | 884 | 0 | 73 | 2 | 85 | 179 | 654 | 20 |
20039960 | 200 | ENGE | 5/20/2009 | 5/27/2009 | 5/25/2009 | 6/1/2009 | 3 | 1 | 236 | 270 | 274 | 0 | 32 | 0 | 0 | 0 | 253 | 1 |
20039961 | 200 | ENGE | 5/27/2009 | 5/28/2009 | 5/25/2009 | 6/1/2009 | 7 | 4 | 424 | 452 | 476 | 0 | 49 | 0 | 18 | 18 | 438 | 3 |
20039979 | 200 | ENGE | 5/28/2009 | 6/15/2009 | 6/15/2009 | 6/22/2009 | 10 | 4 | 649 | 691 | 695 | 0 | 101 | 0 | 0 | 0 | 671 | 1 |
20039987 | 200 | ENGE | 6/15/2009 | 6/23/2009 | 6/22/2009 | 6/29/2009 | 13 | 7 | 735 | 801 | 820 | 0 | 107 | 0 | 0 | 0 | 762 | 7 |
20039993 | 200 | ENGE | 6/23/2009 | 6/29/2009 | 6/29/2009 | 7/6/2009 | 82 | 36 | 3997 | 4212 | 4303 | 0 | 574 | 2 | 54 | 54 | 4098 | 19 |
20040007 | 200 | ENGE | 7/9/2009 | 7/13/2009 | 7/13/2009 | 7/20/2009 | 54 | 15 | 2322 | 2334 | 2746 | 0 | 363 | 4 | 15 | 400 | 2308 | 34 |
20040009 | 200 | ENGE | 7/13/2009 | 7/15/2009 | 7/13/2009 | 7/20/2009 | 18 | 11 | 325 | 529 | 807 | 0 | 48 | 4 | 143 | 282 | 439 | 41 |
20040022 | 200 | ENGE | 7/15/2009 | 7/28/2009 | 7/27/2009 | 8/3/2009 | 52 | 22 | 2402 | 2475 | 3526 | 0 | 339 | 12 | 277 | 1054 | 2411 | 98 |
20040023 | 200 | ENGE | 7/28/2009 | 7/29/2009 | 7/27/2009 | 8/3/2009 | 9 | 5 | 649 | 693 | 1140 | 0 | 92 | 6 | 27 | 444 | 664 | 46 |
20040036 | 200 | ENGE | 7/29/2009 | 8/11/2009 | 8/10/2009 | 8/17/2009 | 56 | 3 | 656 | 726 | 746 | 0 | 104 | 0 | 21 | 21 | 691 | 3 |
20040045 | 200 | ENGE | 8/11/2009 | 8/20/2009 | 8/17/2009 | 8/24/2009 | 65 | 19 | 1343 | 1615 | 1873 | 0 | 229 | 4 | 107 | 260 | 1509 | 34 |
20040049 | 200 | *GIXX | 8/20/2009 | 8/24/2009 | 8/24/2009 | 8/31/2009 | 3 | 4 | 259 | 96 | 103 | 0 | 5 | 0 | 6 | 6 | 77 | 2 |
THIS IS A BETTER SAMPLE
MPGUnitNo | MPGDriverCode | MPGStartDate | MPGEndDate | MPGTotalDistance | MPGFuelConsumed |
3760 | WALLY | 8/3/2009 | 8/10/2009 | 1713 | 229 |
3760 | WALLY | 8/10/2009 | 8/18/2009 | 913 | 115 |
3760 | WALLY | 8/18/2009 | 8/24/2009 | 572 | 99 |
3760 | WALLY | 8/24/2009 | 8/31/2009 | 2644 | 379 |
3760 | WALLY | 8/31/2009 | 9/8/2009 | 1777 | 270 |
3760 | WALLY | 9/8/2009 | 9/14/2009 | 912 | 109 |
3760 | WALLY | 9/14/2009 | 9/17/2009 | 1366 | 171 |
3760 | WALLY | 9/17/2009 | 9/21/2009 | 1248 | 153 |
3760 | WALLY | 9/21/2009 | 9/25/2009 | 1362 | 193 |
3760 | WALLY | 9/25/2009 | 9/28/2009 | 1102 | 154 |
3760 | WALLY | 9/28/2009 | 10/1/2009 | 359 | 44 |
3760 | WALLY | 10/1/2009 | 10/5/2009 | 1429 | 179 |
3760 | WALLY | 10/5/2009 | 10/12/2009 | 2275 | 351 |
3760 | WALLY | 10/13/2009 | 10/19/2009 | 907 | 114 |
3760 | WALLY | 10/19/2009 | 10/26/2009 | 707 | 91 |
3760 | WALLY | 10/26/2009 | 11/3/2009 | 1008 | 131 |
3760 | WALLY | 11/3/2009 | 11/5/2009 | 1238 | 159 |
THIS IS A BETTER SAMPLE
MPGUnitNo | MPGDriverCode | MPGStartDate | MPGEndDate | MPGTotalDistance | MPGFuelConsumed |
3760 | WALLY | 8/3/2009 | 8/10/2009 | 1713 | 229 |
3760 | WALLY | 8/10/2009 | 8/18/2009 | 913 | 115 |
3760 | WALLY | 8/18/2009 | 8/24/2009 | 572 | 99 |
3760 | WALLY | 8/24/2009 | 8/31/2009 | 2644 | 379 |
3760 | WALLY | 8/31/2009 | 9/8/2009 | 1777 | 270 |
3760 | WALLY | 9/8/2009 | 9/14/2009 | 912 | 109 |
3760 | WALLY | 9/14/2009 | 9/17/2009 | 1366 | 171 |
3760 | WALLY | 9/17/2009 | 9/21/2009 | 1248 | 153 |
3760 | WALLY | 9/21/2009 | 9/25/2009 | 1362 | 193 |
3760 | WALLY | 9/25/2009 | 9/28/2009 | 1102 | 154 |
3760 | WALLY | 9/28/2009 | 10/1/2009 | 359 | 44 |
3760 | WALLY | 10/1/2009 | 10/5/2009 | 1429 | 179 |
3760 | WALLY | 10/5/2009 | 10/12/2009 | 2275 | 351 |
3760 | WALLY | 10/13/2009 | 10/19/2009 | 907 | 114 |
3760 | WALLY | 10/19/2009 | 10/26/2009 | 707 | 91 |
3760 | WALLY | 10/26/2009 | 11/3/2009 | 1008 | 131 |
3760 | WALLY | 11/3/2009 | 11/5/2009 | 1238 | 159 |