[Discussion] NetWorkDays function, holidays argument format

[#1]

Hello community, I have started this discussion in order to get a question solved that me and some other members found on another thread and I think that might be of general interest.

On my way on finding a optimal way of calculating the networkdays between two dates but without including the holidays for differetn countries (see thread: Re: Bank holidays for multiple countries) I have prepared the following script in order to test the function:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';


HOLIDAYS:
LOAD * INLINE [START_DATE, END_DATE, HOLIDAY_DAYS
'1/2/2016', '13/2/2016', '10/02/2016',
'1/2/2016', '13/2/2016', '11/02/2016',
'1/2/2016', '13/2/2016', '9/02/2016'];


LEFT JOIN(HOLIDAYS)
LOAD CONCAT(chr(39) & HOLIDAY_DAYS & chr(39), ',') AS HOLIDAYS
RESIDENT HOLIDAYS;


LEFT JOIN(HOLIDAYS)
LOAD NETWORKDAYS(START_DATE, END_DATE, HOLIDAYS) AS WITH_HOL,
     NETWORKDAYS(START_DATE, END_DATE) AS NO_HOL
RESIDENT HOLIDAYS;

The results obtained from running the script:

As you can see, the field WITH_HOL is not properly calculated. So if we look at the formula LOAD NETWORKDAYS(START_DATE, END_DATE, HOLIDAYS) AS WITH_HOL, the field 'HOLIDAYS' is not beeing understood by the function. So my question is, why? if you put the content of the field into a variable, is properly understood but you loose the 'dynamic' property of the field, I mean, the multiple possible values for each country or customer the field may have. Until now, the only way to make the parameter switch is to perform  a loop but for large amounts of data this is not efficient.
If someone can provide me an answer or a solution. Thank you all in advance.

D.

[jpenuliar]

DATA:

    LOAD IDCustomer,

	CustomerCountry,
	[Start Date],
	[End Date]

    FROM

    Sample.xlsx

    (ooxml, embedded labels, table is DATA);

    HOLIDAYLIST:

    LOAD COUNTRY,

concat(chr(39) & DATE & chr(39),',') as List

    FROM

    Sample.xlsx

    (ooxml, embedded labels, table is [Bank Holiday])

    Group by COUNTRY;

    let noRows = NoOfRows('HOLIDAYLIST')-1;

    for i=0 to $(noRows)

	let vCountry = peek('COUNTRY',$(i),'HOLIDAYLIST');
	let vBankHolidays = peek('List',$(i),'HOLIDAYLIST');
	TEST:
	LOAD
		IDCustomer,
		CustomerCountry,
		[Start Date],
		[End Date],
		NetWorkDays([Start Date],[End Date]) as ServiceTime ,
	NetWorkDays([Start Date],[End Date],$(vBankHolidays)) as ServiceTime_holiday_variable
	Resident DATA
	Where
	CustomerCountry =     '$(vCountry)'
	;

    next i;

    Drop table HOLIDAYLIST;

    Drop table DATA;