Bank holidays for multiple countries

[#1]

Hello everyone and sorry beforehand for my possible spelling mistakes as english is not my main language. I have a problem, I've searched for information to fix it in the community posts and I can't fin a suitable solution for it so here I am explaining it with the hope of someone providing one.

Let's imagine that my data consists on a table like this:

CUSTOMER_DATA
IDCustomer
CustomerCountry
StartDate
EndDate

and I wish to add a new field, obtained in the following way:

ServiceTime = NetWorkDays(StartDate, EndDate)

In this new field I wish to obtain the bussiness days the service has been active BUT taking into account the holidays for each IDCustomer and each country. I mean, for every IDCustomer there will exist a different number of holidays between their StartDate and their EndDate. If I have and Excel file with all the bank holidays for each country, wich I can load into my script, like:

COUNTRY_ID	BANK_HOLIDAY_DATE
Poland	12/03/2016
Poland	13/03/2016
...	...

How I could substract the holidays for every 'ServiceTime' for each 'IDCustomer'? Imagine I have a large number of data.

Thank you in advance.

D.

[Gysbert_Wassenaar]

The values in the list are probably not recognized as dates. Make sure the format of the value is the correct date format.

[#3]

That's what I thought on first instance but I've tried to format the data in many ways (in the qlikview script) with no success. The dates I use are unformated on the excel file ('General' format)  I tried to format each day with date() and with date#() and still don't get the dates correct. Do you know which code must I use?

[Gysbert_Wassenaar]

No, I don't. The date format is defined in your document which I can't read. Perhaps you can use numbers instead: CONCAT( Num(BANK_HOLIDAY_DATE),',') AS HOLIDAY_LIST 

[#5]

Ah it's true sorry and thanks. My date format is DD/MM/YYY. However, are you sure that networkdays works fine with fields? I've tried the following script just to test:

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/2/2016',
'1/2/2016', '13/2/2016', '11/2/2016',
'1/2/2016', '13/2/2016', '9/2/2016'];


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


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

Suprisingly, the holidays are not working properly. If I use the field HOLIDAY_DAYS, it only substracts the first day. So, maybe comes as 'NetworkDays' fuction is bad defined or not accept this kind of fields?

Edit: If I copy exactly what's on the field HOLIDAYS once reloaded: '10/2/2016','11/2/2016','9/2/2016' in the holidays space for the function 'NetWorkdays' it works so maybe the function is not able to read fields?

[Gysbert_Wassenaar]

The function can read fields. But I don't know how to make it understand the contents of one field as a list of parameters instead of one parameter. As a workaround you can try this:

HOLIDAYS: 

LOAD *,   NETWORKDAYS(START_DATE, END_DATE) - NETWORKDAYS(START_DATE, END_DATE, HOLIDAY_DAYS) AS NO_OF_HOLIDAYS_IN_INTERVAL INLINE [START_DATE, END_DATE, HOLIDAY_DAYS 

'1/2/2016', '13/2/2016', '10/2/2016', 

'1/2/2016', '13/2/2016', '11/2/2016', 

'1/2/2016', '13/2/2016', '9/2/2016']; 

NETWORKDAYS:

LOAD DISTINCT

  START_DATE,

  END_DATE,

  SUM(NO_OF_HOLIDAYS_IN_INTERVAL) AS SUM_OFNO_OF_HOLIDAYS_IN_INTERVAL,

  NETWORKDAYS(START_DATE, END_DATE) - SUM(NO_OF_HOLIDAYS_IN_INTERVAL) AS WITH_HOL

RESIDENT

  HOLIDAYS

GROUP BY

  START_DATE, END_DATE

  ;