1 Reply Latest reply: Jan 2, 2013 7:17 AM by Jose Tos RSS

    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.

        • Re: filling blank dates with previous records
          Jose Tos

          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