Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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;