filling blank dates with previous records

Mon, 31 Dec 2012 05:54:55 GMT

10000000	ASDFG	1/1/2012	EUR	LRP	56,987
10000001	ASDFGDSF	2/1/2012	USD	FGT	54,785
16527892	DDD	2/1/2012	PKI	KLI	5000
9654213	PLKJH	2/1/2012	USD	MNI	6000
10000002	ASDFASDFASD	3/1/2012	USD	BGO	62,314
11000003	ASDSASDE	4/1/2012	USD	PBG	56,987
11111111	BKH	4/1/2012	USD	PLK	1,000
12358966	MOIUY	4/1/2012	USD	POL	2,000
89745621	LOI	4/1/2012	EUR	LOKI	200
11000007	ADSFE	12/1/2012	USD	BGO	56,987
11000008	FAFOOOASF	13/1/2012	USD	BGO	54,785
11000009	ADSFLLLL	14/1/2012	USD	BGO	62,314
11000010	ADLFOPP	15/1/2012	USD	BGO	23,268
11000011	AFQER	16/1/2012	USD	BGO	23,654
11000012	INTER LTD.	17/1/2012	USD	BGO	2,828
54812586	LTD	17/1/2012	USD	PBG	6,000
89745621	PLOI	17/1/2012	USD	PGB	2,500
11000013	VSAFD	23/1/2012	USD	BGO	56,987
11000314	ASDFRRYGFS	24/1/2012	EUR	BGO	54,785
11200015	ADSFACVR	25/1/2012	SAR	BGO	62,314
11200216	ASDFEFCC	26/1/2012	SAR	BGO	23,268
11200517	ADSFVHT	27/1/2012	USD	PBG	56,987
11200618	AQEWER	28/1/2012	USD	PBG	54,785
11200619	CVREFASDF	29/1/2012	USD	PBG	62,314
11200620	ASDFCWE	30/1/2012	USD	PBG	56,987

I have a following data, in this data i have some dates missing like 5/1/2012 , 9-11/1/2012, 18-22/1/2012 concider these are the holidays and there is no record in them what i want is that in these dates, the previous date ALL customers record should be copied or repeated. i need the script for. if there is a difference of one date like 5/1/2012 in that i need all the customers of 4/1/2012 and from 9-11/1/2012 i need the record of 8/1/2012

i have used date dimension too.

Re: filling blank dates with previous records

chematos — Wed, 02 Jan 2013 12:17:19 GMT

First of all, you need get the min and max date of your data in variables and create all dates in a table using that variables. Order you fact table by Date and use peek() function to get min and max dates.

After that, you could create a table with all your dates, theres is a lot of examples how to create a master calendar, you can use that to create dates from vMinDate to vMaxDate and do a left join by Date of your fact table with this table that has only dates for everyday:

left Join

load * resident

TableXXX;

Then, you load the same table but with the next logic, if your numeric fields are null values, you use the value of the previous day, so you use peek() function to get the last value loaded like this:

Load Date,

if(isnull(NumField), peek('NumField'), NumField) as NumField

....

resident Fact_Table;

Hope this helps

topic Re: filling blank dates with previous records in QlikView

filling blank dates with previous records

Re: filling blank dates with previous records