topic How to exclude holidays from network days for more then one country ? in App Development

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

VKG — Sat, 16 Nov 2024 18:32:50 GMT

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.

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

martinpohl — Wed, 03 Jun 2020 13:26:23 GMT

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

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

VKG — Wed, 03 Jun 2020 13:35:20 GMT

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.

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

martinpohl — Wed, 03 Jun 2020 18:48:01 GMT

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