topic Networkdays holidays in QlikView

Networkdays holidays

Thu, 29 Jul 2010 08:17:07 GMT

Dear all,

I have created some variables on my system with holidays.

I want to load a new table where Field data is exactly as variable name, and I want to call that variable to get holidays.

Any help

Example:

Variables:

1 '01/01/2010','02/01/2010','18/08/2010'

2 '02/02/2010','15/07/2011'

TABLE

FIELDA,FROM,TO

1,17/07/2010,25/08/2010

1,20/08/2010,25/08/2010

2,17/07/2010,25/08/2010

2,20/08/2010,25/08/2010

I want to do.

TABLE:

LOAD

networkdays(FROM,TO,$(FIELDA)) AS BLA

I want to use the holidays variable that name = FIELDA value in each reg

Best regards,

Networkdays holidays

jonathandienst — Thu, 29 Jul 2010 08:29:56 GMT

Jordi

I use this script:

/*
Load holiday data for working day calculations
*/

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

// Save holidays as a string (comma separated list of
// single quoted dates) into the variable vPublicHolidays
tmpConcat:
LOAD concat(chr(39) & Date & chr(39),',') AS HolidayDates
RESIDENT tmpHoliday;

LET vPublicHolidays = fieldvalue('HolidayDates',1);

// The holiday tables are not needed
DROP TABLE tmpHoliday;
DROP TABLE tmpConcat;

This uses a spreadsheet with the two columns

PUBLIC HOLIDAY - name of holiday
DATE - date of holiday

The result variable is vPublicHolidays

It does not handle ranges like your example, but neither do the working day formulae in QV.

Hope this helps

Jonathan

Networkdays holidays

Thu, 29 Jul 2010 09:01:49 GMT

Dear Jonathan,

I can't use your example

In your source all holidays are on one variable.

On my case, each variable has his own holidays, and i need to use in each record diferent variables and not only vPublicHolidays.

Best regards,