topic AW:Re: Filling in Date Ranges in QlikView

Filling in Date Ranges

Tue, 10 Nov 2009 06:36:04 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

disqr_rm — Tue, 10 Nov 2009 09:06:21 GMT

Here you go:

DriveRecords:
LOAD * INLINE [
StartDate, EndDate, MPG, Driver
10/19/2009, 10/26/2009, 9.526, PATAN
10/22/2009, 11/26/2009, 2.126, PATAN1
];

AllDates:
LOAD DISTINCT date#(StartDate) as Date
RESIDENT DriveRecords;
LOAD DISTINCT date#(EndDate) as Date
RESIDENT DriveRecords;

MinMax:
Load min(Date) as MinDate,
max(Date) as MaxDate
resident AllDates
group by 1;

Let vMinDate = peek('MinDate');
Let vMaxDate = peek('MaxDate');
Let vDays = vMaxDate - vMinDate + 1;

drop tables AllDates, MinMax;

FilldDates:
Load date($(vMinDate) + recno() - 1) as Date
AUTOGENERATE $(vDays);

Left join (DriveRecords)
IntervalMatch (Date)
LOAD StartDate, EndDate
RESIDENT DriveRecords;

drop table FilldDates;

AW:Re: Filling in Date Ranges

Tue, 10 Nov 2009 16:52:41 GMT

Hi,

nice Skript!

A little suggestion. If you dont drop the table FilldDates you can also see dates where no driver is set in the table DriveRecords. This happens when the intervals StartDate - EndDate doesn't overlap or fit together.

Eg:

DriveRecords:
LOAD * INLINE [
StartDate, EndDate, MPG, Driver
10/19/2009, 10/26/2009, 9.526, PATAN
10/28/2009, 11/26/2009, 2.126, PATAN1
];

AW:Re: Filling in Date Ranges

Tue, 10 Nov 2009 23:19:15 GMT

It worked.

Thanks a bunch.

The only issue is my computer crashes if i pull in more than 10 days worth of data. is there a less memory intensive why to accomplish this?

if not maybe i will just have to run reloads in the production server while i am still developing it on my desktop.

any suggestions would be welcome.