4 Replies Latest reply: Nov 12, 2015 10:47 AM by Martijn Schulte RSS

    Creating missing dates

    Arjan IJlenhave

      Hi all,

       

      A table has the following data and the current date is 11-11-2015.

       

      CustomerPolisNumberJobDateAmount
      123456123456-VPB-11-1-2015150
      654321654321-COP-11-1-2015300
      654321654321-COP-11-4-2015250
      654321654321-COP-11-7-2015175
      654321654321-COP-11-10-2015450
      456789456789-TTP-11-7-2015750
      999556999556-TTP-11-1-2015150
      999556999556-TTP-11-2-2015150
      999556999556-TTP-11-3-2015150
      999556999556-TTP-11-4-2015150
      999556999556-TTP-11-5-2015150
      999556999556-TTP-11-6-2015150
      999556999556-TTP-11-7-2015150
      999556999556-TTP-11-8-2015150
      999556999556-TTP-11-9-2015150
      999556999556-TTP-11-10-2015150
      999556999556-TTP-11-11-2015150

       

      I want to create the following table data:

       

      CustomerPolisNumberJobDateAmount
      123456123456-VPB-11-1-2015150
      123456123456-VPB-11-2-20150
      123456123456-VPB-11-3-20150
      123456123456-VPB-11-4-20150
      123456123456-VPB-11-5-20150
      123456123456-VPB-11-6-20150
      123456123456-VPB-11-7-20150
      123456123456-VPB-11-8-20150
      123456123456-VPB-11-9-20150
      123456123456-VPB-11-10-20150
      123456123456-VPB-11-11-20150
      654321654321-VPB-11-1-2015300
      654321654321-VPB-11-2-20150
      654321654321-VPB-11-3-20150
      654321654321-VPB-11-4-2015250
      654321654321-VPB-11-5-20150
      654321654321-VPB-11-6-20150
      654321654321-VPB-11-7-2015175
      654321654321-VPB-11-8-20150
      654321654321-VPB-11-9-20150
      654321654321-VPB-11-10-2015450
      654321654321-VPB-11-11-20150
      456789456789-TTP-11-1-20150
      456789456789-TTP-11-2-20150
      456789456789-TTP-11-3-20150
      456789456789-TTP-11-4-20150
      456789456789-TTP-11-5-20150
      456789456789-TTP-11-6-20150
      456789456789-TTP-11-7-2015750
      456789456789-TTP-11-8-20150
      456789456789-TTP-11-9-20150
      456789456789-TTP-11-10-20150
      456789456789-TTP-11-11-20150
      999556999556-TTP-11-1-2015150
      999556999556-TTP-11-2-2015150
      999556999556-TTP-11-3-2015150
      999556999556-TTP-11-4-2015150
      999556999556-TTP-11-5-2015150
      999556999556-TTP-11-6-2015150
      999556999556-TTP-11-7-2015150
      999556999556-TTP-11-8-2015150
      999556999556-TTP-11-9-2015150
      999556999556-TTP-11-10-2015150
      999556999556-TTP-11-11-2015150

       

      Can you help me?

       

      Best regards,

      Arjan

        • Re: Creating missing dates
          Miguel Braga

          Hey there,

           

          Have you tried to make a Master Calendar?

           

          Thanks,

          MB

          • Re: Creating missing dates
            Mark Little

            Hi,

             

            I reckon you will be looking at an interval match

             

            take a look at the linked doc.

            Interval Match Feature/Function

             

            Mark

            • Re: Creating missing dates
              Settu Periyasamy

              Hi,

              check the below script. may be helps. May Date format is (M/D/YYYY)

              T1:
              LOAD * INLINE [
                  Customer, PolisNumber, JobDate, Amount
                  123456, 123456-VPB-1, 1/1/2015, 150
                  654321, 654321-COP-1, 1/1/2015, 300
                  654321, 654321-COP-1, 4/1/2015, 250
                  654321, 654321-COP-1, 7/1/2015, 175
                  654321, 654321-COP-1, 10/1/2015, 450
                  456789, 456789-TTP-1, 7/1/2015, 750
                  999556, 999556-TTP-1, 1/1/2015, 150
                  999556, 999556-TTP-1, 2/1/2015, 150
                  999556, 999556-TTP-1, 3/1/2015, 150
                  999556, 999556-TTP-1, 4/1/2015, 150
                  999556, 999556-TTP-1, 5/1/2015, 150
                  999556, 999556-TTP-1, 6/1/2015, 150
                  999556, 999556-TTP-1, 7/1/2015, 150
                  999556, 999556-TTP-1, 8/1/2015, 150
                  999556, 999556-TTP-1, 9/1/2015, 150
                  999556, 999556-TTP-1, 10/1/2015, 150
                  999556, 999556-TTP-1, 11/1/2015, 150
              ];
              
              
              NoConcatenate
              T2:
              Load Distinct Customer,PolisNumber Resident T1;
              Join(T2)
              Load * Inline [
              JobDate
              1/1/2015
              2/1/2015
              3/1/2015
              4/1/2015
              5/1/2015
              6/1/2015
              7/1/2015
              8/1/2015
              9/1/2015
              10/1/2015
              11/1/2015
              ];
              Left Join(T2)
              LOAD * Resident T1;
              DROP Table T1;
              
                • Re: Creating missing dates
                  Martijn Schulte

                  On date level:

                   

                  T1: 
                  LOAD * INLINE
                  Customer, PolisNumber, JobDate, Amount 
                  123456, 123456-VPB-1, 1-1-2015, 150
                  654321, 654321-COP-1, 1-1-2015, 300
                  654321, 654321-COP-1, 1-4-2015, 250
                  654321, 654321-COP-1, 1-7-2015, 175
                  654321, 654321-COP-1, 1-10-2015, 450
                  456789, 456789-TTP-1, 1-7-2015, 750
                  999556, 999556-TTP-1, 1-1-2015, 150
                  999556, 999556-TTP-1, 1-2-2015, 150
                  999556, 999556-TTP-1, 1-3-2015, 150
                  999556, 999556-TTP-1, 1-4-2015, 150
                  999556, 999556-TTP-1, 1-5-2015, 150
                  999556, 999556-TTP-1, 1-6-2015, 150
                  999556, 999556-TTP-1, 1-7-2015, 150
                  999556, 999556-TTP-1, 1-8-2015, 150
                  999556, 999556-TTP-1, 1-9-2015, 150
                  999556, 999556-TTP-1, 1-10-2015, 150
                  999556, 999556-TTP-1, 1-11-2015, 150
                  ]



                  T2:
                  NoConcatenate load
                  *,
                  Customer&'|'&PolisNumber as ID
                  Resident T1
                  Order by
                  PolisNumber asc,
                  JobDate Desc ;

                  Drop table T1;

                  T3:
                  load *, if(previous(ID)=ID, previous(JobDate),ToDay()) as NextJobDate
                  Resident T2
                  order by ID, JobDate desc;

                  drop table T2;

                  Resultaat:
                  load
                  ID,
                  Customer,
                  PolisNumber,
                  JobDate,
                  date(JobDate + IterNo() -1) as Date,
                  If(JobDate=date(JobDate + IterNo() -1), Amount,0) as Amount
                  Resident T3
                  while JobDate + IterNo() - 1 < NextJobDate
                  order by
                  ID,
                  JobDate;

                  drop table T3;