topic Filling in Date Ranges in QlikView

Filling in Date Ranges

Tue, 10 Nov 2009 06:36:14 GMT

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

Filling in Date Ranges

Tue, 10 Nov 2009 06:40:08 GMT

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

Filling in Date Ranges

Tue, 10 Nov 2009 06:40:09 GMT

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