1 Reply Latest reply: Feb 9, 2016 6:40 AM by Stefan Wühl RSS

    LastWorkDate

    Elke Corstjens

      I have following data structure:

      Variabele Bank01 = '01/01/2016','02/02/2016'

      Variabele Bank02 = '01/01/2016'

       

      MyTable:

      -------------

      Exclusion Calendar              Payment Date               Customer                     Days

      Bank01                                02/02/2016                    Customer123                1

      Bank01                                04/02/2016                    Customer234                1

      Bank02                                02/02/2016                    Customer458                1

       

      Now I need to apply the function lastworkdate in MyTable. But the holiday calendar is specified in the field Exclusion Calendar.
      I thought the following would work: LastWorkDate([Expected Payment Date],[Days],[Exclusion Calendar]) as [New Payment Date]

      But this isn't. Can someone please help me how to solve?

        • Re: LastWorkDate
          Stefan Wühl

          Maybe like this:

           

          SET Bank01 = "'01/01/2016','02/02/2016'";
          Set Bank02 = "'01/01/2016'";
          
          MyTable:
          LOAD * INLINE [
          Exclusion Calendar,              Payment Date,              Customer,                    Days
          Bank01,                                02/02/2016,                    Customer123,                1
          Bank01,                                04/02/2016,                    Customer234,                1
          Bank02,                                02/02/2016,                    Customer458,                1
          ];
          
          
          For Each vBank in 'Bank01','Bank02'
          
          
          RESULT:
          LOAD *, LastWorkDate([Payment Date], Days, $($(vBank))) as NewPaymentDate;
          LOAD * RESIDENT MyTable Where [Exclusion Calendar] = '$(vBank)';
          
          
          Next vBank
          
          
          DROP TABLE MyTable;
          

           

          You can create some code that retrieves all bank names from your MyTable table to be used in the For Each loop.

           

          Customer Days Exclusion Calendar Payment Date NewPaymentDate
          Customer1231Bank0102/02/201602/03/2016
          Customer2341Bank0104/02/201604/04/2016
          Customer4581Bank0202/02/201602/02/2016