Exclude Holidays

[alec1982]

Hi Guys,

I have the following script within a table to calculate the holiday nr of hours within a week where the total hours of a week is 40

(40-(NetWorkDays(weekStart([Effective Date]),

           weekEnd([Effective Date]),'5/27/2013' ))*8),0) as HolidayHours

It is working but now I am trying to add more dates to it and not sure how should I do it..

Anybody knows how can I add more dates or would it be possible to add the holidays into a table and call them here?

Thxs,

Alec

[luciancotea · Accepted solution]

Use a variable for the 3th parameter of NetworkDays() and fill it with holiday dates.

(40-(NetWorkDays(weekStart([Effective Date]),

           weekEnd([Effective Date]),$( vHolidaysList ) ))*8),0) as HolidayHours

[alec1982]

Hi Lucian,

Would you please provide a sample of the varriable content.

Assuming i have three holiday dates such as

01/01/2013

05/27/2013

09/02/2013

So how should I store that into the varriable.

Thxs,

Alec

[shree909]

If u have holidays  use this

set vholidaylist= '01/01/2013','05/27/2013','09/02/2013';

if u have holidays coming from either excel or  database

check out this    http://yahqblog.blogspot.com/2010_09_01_archive.html

tmpHoliday:
LOAD Date([DATE], 'yyyy/MM/dd') as Date
FROM [..\QVDATA\Public Holidays.xlsx]
(ooxml, embedded labels, table is Sheet1);

tmpConcat:
LOAD concat(chr(39) & Date & chr(39),',') AS HolidayDates
RESIDENT tmpHoliday;

Let vholidaylist= fieldvalue('HolidayDates',1);

DROP TABLE tmpHoliday;
DROP TABLE tmpConcat;