Qlik Community

Ask a Question

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

How to exclude holidays from network days for more then one country ?

Hi,

Is it possible to exlcude holidays as per respective country while calcualting lead time between start & end date . 

So sample data as given below.  

Order NoCountryStart DateEnd DateLead time
123UK01-01-2020-01-2011
234Canada02-01-2021-01-2011
456US03-01-2022-01-2013
678UK04-01-2023-01-2013
789US05-01-2024-01-2014
890Canada06-01-2025-01-2014

 

Holiday Table  - 

CountryHoliday
UK01-01-20
UK02-01-20
UK06-01-20
US01-01-20
US02-01-20
US06-01-20
Canada03-01-20
Canada02-01-20
Canada10-01-20

 

I want to deduct country specific holidays as per second table. Is there any way to do so? 

 

Thanks.

3 Replies
Partner
Partner

I would do:

create three variables:

holidayUK = '01.01.2020','02.01.2020','06.01.2020'

holidayCD = '01.01.2020','02.01.2020','06.01.2020'

holidayUS = '02.01.2020','03.01.2020','10.01.2020'

then in script:

if(Country='US',networkdays,startdate,enddate,$(holidayUS),

if(Country='UK',networkdays,startdate,enddate,$(holidayUK),

if(Country='Canada',networkdays,startdate,enddate,$(holidayCD),networkdays,startdate,enddate))) as Networkdays

Regards

Contributor
Contributor

Thank you for your reply. 

Actually, it's just a sample data. I have a complete database table for country holidays. THis table has around 50 countries with holidays. That will need to use. 

Any way to use table for holiday list. 

Partner
Partner

Yes you can.
Create your variables in script
ConHolidays:
load
Country,
concat(Holiday,chr(39)&','&chr(39)) as HolidayCon
resident table
group by Country;
let Countries = noofrows('ConHolidays')-1;
for tablerow = 0 to $(Contries)
let Country = peek('Country',$(tablerow),'ConHolidays');
let holiday_$(Country) = chr(39)& peek('HolidayCon',$(tablerow),'ConHolidays') &chr(39);
next
so you will get a variable holiday for each country that you can use in your script for networkdays, depending on country in datas.
Regards