Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

LastWorkDate

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?

1 Reply
MVP
MVP

Re: LastWorkDate

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