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

[VKG]

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 No	Country	Start Date	End Date	Lead time
123	UK	01-01-20	20-01-20	11
234	Canada	02-01-20	21-01-20	11
456	US	03-01-20	22-01-20	13
678	UK	04-01-20	23-01-20	13
789	US	05-01-20	24-01-20	14
890	Canada	06-01-20	25-01-20	14

Holiday Table  - 

Country	Holiday
UK	01-01-20
UK	02-01-20
UK	06-01-20
US	01-01-20
US	02-01-20
US	06-01-20
Canada	03-01-20
Canada	02-01-20
Canada	10-01-20

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

Thanks.

[martinpohl]

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

[VKG]

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. 

[martinpohl]

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