topic Re: Exclude Holidays in QlikView

Exclude Holidays

alec1982 — Thu, 27 Jun 2013 19:03:59 GMT

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

Re: Exclude Holidays

luciancotea — Thu, 27 Jun 2013 20:13:17 GMT

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

Re: Exclude Holidays

alec1982 — Thu, 27 Jun 2013 20:18:48 GMT

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

Re: Exclude Holidays

shree909 — Thu, 27 Jun 2013 21:55:24 GMT

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;