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


      I have following data structure:

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

      Variabele Bank02 = '01/01/2016'




      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'";
          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'
          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