3 Replies Latest reply: Nov 4, 2016 4:27 AM by Mike Spada RSS

    Last month and date missing

    Mike Spada

      Hi,

      I have a table with date and values. In loading script I need to take the value referred to the date 1 month before... this could be simple with AddMonths function (and I'm able to to this :-) ) but what if the date is missing and I need to recover the previous populated date?

       

      For example:

       

        

      DateMyValue
      07/09/201633
      08/09/201611
      09/09/201623
      12/09/201644
      13/09/201632
      14/09/201643
      09/10/201677
      10/10/201665
      11/10/201645

       

      with vTodayDate = 11/10/2016, 1 month before is AddMonths(vTodayDate, -1) --> 11/09/2016... but I haven't it. I need to consider 09/09/2016 because is the previous useful date.

       

      How can I do this?

      Any idea?

       

      Thanks in advance,

      Mike

        • Re: Last month and date missing
          Marco Wedel

          maybe first loading

          AddMonths(vTodayDate, -1)  as PreviousMonthDate

          and in a second resident load join the Maximum Date <= PreviousMonthDate.

            • Re: Last month and date missing
              Simone Spanio

              I agree with Marco,

              maybe you could try with first n load (because you don't know how many dates are missing... one? two? Ten?).

              If DataTable is the name of the table loaded, you could do something like this:

               

              SET vToday = Date('11/10/2016', 'DD/MM/YYYY');

               

               

              MyTable:

              LOAD * INLINE [

                  Date, MyValue

                  07/09/2016, 33

                  08/09/2016, 11

                  09/09/2016, 23

                  12/09/2016, 44

                  13/09/2016, 32

                  14/09/2016, 43

                  09/10/2016, 77

                  10/10/2016, 65

                  11/10/2016, 45

              ];

               

               

              NoConcatenate

               

               

              AMonthAgo:

              FIRST 10 LOAD Date, //if you need you can increase the number of record (for example if you have many days of jump)

                MyValue

              Resident MyTable

              Where Date <= AddMonths($(vToday), -1);

               

               

              Inner Join

               

               

              LOAD Max(Date) as Date

              Resident AMonthAgo;

               

               

              DROP Table MyTable;

               

              Let us know if what you are looking for :-)

              S.