Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 |
---|---|---|---|---|
Customer123 | 1 | Bank01 | 02/02/2016 | 02/03/2016 |
Customer234 | 1 | Bank01 | 04/02/2016 | 04/04/2016 |
Customer458 | 1 | Bank02 | 02/02/2016 | 02/02/2016 |