topic Re: Bank holidays for multiple countries in QlikView

Bank holidays for multiple countries

Wed, 17 Aug 2016 07:34:43 GMT

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.

Re: Bank holidays for multiple countries

Gysbert_Wassenaar — Wed, 17 Aug 2016 09:08:40 GMT

Perhaps like this:

mapHolidays:

MAPPING LOAD

COUNTRY_ID,

Concat(BANK_HOLIDAY_DATE, ',') as List

FROM

...source data...

GROUP BY COUNTRY_ID

;

MyTable:

LOAD

IDCustomer,

CustomerCountry,

StartDate,

EndDate,

NetWorkDays(StartDate, EndDate, ApplyMap('mapHolidays',CustomerCountry)) as ServiceTime

FROM

...source data...

;

Re: Bank holidays for multiple countries

Wed, 17 Aug 2016 10:13:39 GMT

Hi and thanks for answering! I think that migh solve my problem but I need one step further. When I use the code you provided (thanks again) and there's a country that doesn't have holidays in the list (maybe because there are no bank holidays in that country) so it doesn't appear and it's not calculated. Do you know what to do?

Thanks in advance
D.

Re: Bank holidays for multiple countries

Gysbert_Wassenaar — Wed, 17 Aug 2016 12:30:49 GMT

If a country has no bank holidays then it will not be in the mapping table. In that case ApplyMap will put the country name in the networkdays function. Since that's not a valid date it will be ignored by the networkdays function.

Re: Bank holidays for multiple countries

Thu, 18 Aug 2016 16:00:59 GMT

Hello, I've tried the solution in my code but with no succeed, the bank holidays are not being substacted. Do you have any idea why? And how to fix?

My idea is that Networkdays admits the dates only in 'string' format or string variable format and using applymap maybe we're getting a field. No idea how to convert a field into the type thet the function admits.

Re: Bank holidays for multiple countries

trdandamudi — Thu, 18 Aug 2016 16:27:26 GMT

See if the below link is useful...

Re: Using networkdays and public holidays in qlikview

Re: Bank holidays for multiple countries

Thu, 18 Aug 2016 16:33:53 GMT

Hi, thanks a lot for the contribution. I've already visisted that thread and that's the way I have it implemented now in my app. Tha fact is that I calculate more things and I have a very LARGE ammount of data so the script takes a lot of time plus I need to reload my tool every day several times so I'm searching for a lighter way to do it.

Re: Bank holidays for multiple countries

Mon, 22 Aug 2016 10:04:35 GMT

The problem is that the function 'Networkdays' doesn't seem to accept some kinds of inputs at the third condition such as fields. It seem to be only accepting variables. Is there way to make a variable 'dynamic', I mean, like changing it's value for every IDCustomer with the suitable holidays. Thanks in advance.

Re: Bank holidays for multiple countries

Gysbert_Wassenaar — Mon, 22 Aug 2016 11:50:28 GMT

Networkdays will accept fields as well, but (unless you use peek or lookup) only fields that exist in the source table you're loading the data from.

Re: Bank holidays for multiple countries

Tue, 23 Aug 2016 07:28:14 GMT

I've tried to do something like this in first instace:

HOLIDAYS:
LOAD
    COUNTRY_ID,
    CONCAT(chr(39) & BANK_HOLIDAY_DATE & chr(39),',') AS HOLIDAY_LIST
FROM ../../Source
GROUP BY COUNTRY_ID;

TABLE1:
LOAD
    IDCustomer,
    CustomerCountry,
    StartDate,
    EndDate
FROM ../../Source;

LEFT JOIN (TABLE1)
LOAD COUNTRY_ID as CustomerCountry,
     HOLIDAY_LIST
RESIDENT HOLIDAYS;

LEFT JOIN (TABLE1)
LOAD IDCustomer,
     CustomerCountry,
     NetWorkDays(StartDate, EndDate, HOLIDAY_LIST) as ServiceTime
RESIDENT TABLE1;

But it doesn't seem to works as it's not substracting the holidays. Do you have any idea what I'm doing wrong? Thank you a lot.

Re: Bank holidays for multiple countries

Gysbert_Wassenaar — Tue, 23 Aug 2016 10:19:14 GMT

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

Re: Bank holidays for multiple countries

Tue, 23 Aug 2016 11:27:11 GMT

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?

Re: Bank holidays for multiple countries

Gysbert_Wassenaar — Tue, 23 Aug 2016 11:52:43 GMT

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

Re: Bank holidays for multiple countries

Tue, 23 Aug 2016 12:32:48 GMT

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?

Re: Bank holidays for multiple countries

Gysbert_Wassenaar — Wed, 24 Aug 2016 16:16:21 GMT

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

;