Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

filling blank dates with previous records

10000000ASDFG1/1/2012EURLRP56,987
10000001ASDFGDSF2/1/2012USDFGT54,785
16527892DDD2/1/2012PKIKLI5000
9654213PLKJH2/1/2012USDMNI6000
10000002ASDFASDFASD3/1/2012USDBGO62,314
11000003ASDSASDE4/1/2012USDPBG56,987
11111111BKH4/1/2012USDPLK1,000
12358966MOIUY4/1/2012USDPOL2,000
89745621LOI4/1/2012EURLOKI200
11000007ADSFE12/1/2012USDBGO56,987
11000008FAFOOOASF13/1/2012USDBGO54,785
11000009ADSFLLLL14/1/2012USDBGO62,314
11000010ADLFOPP15/1/2012USDBGO23,268
11000011AFQER16/1/2012USDBGO23,654
11000012INTER LTD.17/1/2012USDBGO2,828
54812586LTD17/1/2012USDPBG6,000
89745621PLOI17/1/2012USDPGB2,500
11000013VSAFD23/1/2012USDBGO56,987
11000314ASDFRRYGFS24/1/2012EURBGO54,785
11200015ADSFACVR25/1/2012SARBGO62,314
11200216ASDFEFCC26/1/2012SARBGO23,268
11200517ADSFVHT27/1/2012USDPBG56,987
11200618AQEWER28/1/2012USDPBG54,785
11200619CVREFASDF29/1/2012USDPBG62,314
11200620ASDFCWE30/1/2012USDPBG56,987

Hi

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.

1 Reply
chematos
Valued Contributor II

Re: filling blank dates with previous records

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