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