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]

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...

     ;

[#3]

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.

[Gysbert_Wassenaar]

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.

[#5]

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.

[trdandamudi]

See if the below link is useful...

Re: Using networkdays and public holidays in qlikview

[#7]

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.

[#8]

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.

[Gysbert_Wassenaar]

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.

[#10]

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.