topic How to Find LastWorkDay with different holiday lists in QlikView

How to Find LastWorkDay with different holiday lists

Fri, 16 Dec 2016 05:44:09 GMT

Hi All,

Need Help!!!!

I have a 2 tables see below:

Table:
LOAD * INLINE [
Country, Date

US, 30-12-2016
Canada, 01-01-2017
Italy, 30-12-2017
US, 27-05-2017
US, 23-11-2017
Italy,05-01-2017
US, 03-07-2017
]

LOAD * INLINE [
Country, Tolerance
US, 2
Canada, 3
Italy, 1
];

LOAD * INLINE [
Country, Holidays

US, 02-01-2017

US, 29-05-2017

US, 04-07-2017

US, 04-09-2017

US, 23-11-2017

US,24-11-2017

US, 25-12-2017

Canada, 01-01-2017

Canada, 20-02-2017

Canada, 14-04-2017

Canada, 22-05-2017

Canada, 01-07-2017

Canada,07-08-2017

Canada, 04-09-2017

Canada, 09-10-2017

Canada, 25-12-2017

Italy, 01-01-2017

Italy, 06-01-2017

Italy, 06-04-2017

Italy, 17-04-2017

Italy, 25-04-2017

Italy,01-05-2017

Italy, 02-06-2017

Italy, 15-08-2017

Italy, 25-12-2017

];

Or if you want holidays in variable the use this:

Set vUS= '02-01-2017', '29-05-2017', 04-07-2017, '04-09-2017', '23-11-2017', '24-11-2017', '25-12-2017';

Set vCanada= '01-01-2017', '20-02-2017', '14-04-2017', '22-05-2017', '01-07-2017','07-08-2017', '04-09-2017', '09-10-2017', '25-12-2017';

Set vItaly= '01-01-2017', '06-01-2017', '06-04-2017', '17-04-2017', '25-04-2017','01-05-2017', '02-06-2017', '15-08-2017', '25-12-2017';

Now in Date field I want to add Tolerance days. So here I want Weekends and holidays excluded from the New Date field, So the problem is I have multiple list of Holiday based on the Country.

Conditions: If the Date is on Weekends or Holiday than Date will start from Next Working Day.

Final outcome from upper tables I want this result:

Country, Date

US, 04-01-2016 (Here I have excluded [Sat,Sun-Weekends],Mon(Holiday),[Fri,Mon-Tolerance])
Canada, 05-01-2017 (Here Date is on Sun so It will starts from Mon- So I have excluded [Mon,Tue,Wed])
Italy, 30-12-2017 (Here I have excluded [Sat,Sun-Weekends],[Fri-Tolerance])
US, 27-05-2017 (Here Date is on Sat so it will start from Tue because Mon has holiday-[Tue,Wed- Tolerance])
US, 29-11-2017 (Here Date is on holiday[Thr] and it will also skip [Fri-(Holiday)] and [Sat,Sun-Weekends] it will start from Mon-[Mon,Tue-Tolerance])
Italy,09-01-2017 (Here [Fri-Holiday] and [Sat,Sun-Weekends]. So I have excluded [Thr- Tolerance])
US, 06-07-2017 (Here [Tue-Holiday]. So I have excluded [Mon,Wed- Tolerance])

Regards,

Pawan

Re: How to Find LastWorkDay with different holiday lists

Fri, 16 Dec 2016 05:59:02 GMT

Sorry to forget:

1st Table is Raw Data

2nd Table is Tolerance Table

3rd Table is Holiday Table

Please be note that Raw data has 10,00,000 records and I have 5 to 6 Country holiday lists as well. So create the code according to that.

Regards,

Pawan

Re: How to Find LastWorkDay with different holiday lists

avinashelite — Fri, 16 Dec 2016 06:25:48 GMT

Try like this :

Use the

networkdays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

networkdays ('2007-02-19', '2007-03-01') returns 9

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

With an if condition so that it selectively takes the holiday according to the country

Re: How to Find LastWorkDay with different holiday lists

Mon, 19 Dec 2016 07:35:13 GMT

Hi Avinash,

Thanks for reply!!!

But here I have One Date and Tolerance Days and from that I required Next Workday.

I am using LastWorkDate Function and Script as follows:

Set US= '01/02/2017', '05/29/2017', '07/04/2017', '09/04/2017', '11/23/2017', '11/24/2017', '12/25/2017', '12/26/2017', '12/27/2017', '12/28/2017', '12/29/2017';

Set Canada= '01/01/2017', '02/20/2017', '04/14/2017', '05/22/2017', '07/01/2017','08/07/2017', '09/04/2017', '10/09/2017', '12/25/2017';

Set Italy= '10/12/2016', '01/01/2017', '01/06/2017', '04/16/2017', '04/17/2017', '04/25/2017', '05/01/2017', '06/02/2017', '08/15/2017','11/1/2017','12/08/2017','12/25/2017', '12/26/2017';

Table1:

LOAD * INLINE [

Country, Date

US, 30-12-2016

Canada, 01-01-2017

Italy, 30-12-2017

US, 27-05-2017

US, 23-11-2017

Italy,05-01-2017

US, 03-07-2017

];

Left Join

LOAD * INLINE [

Country, Tolerance

US, 2

Canada, 3

Italy, 1

];

Table2:

Load

Country,

Date,

Tolerance,

LastWorkDate(Date,[Tolerance],if(Country='Italy',$(Italy),If(Country='US',$(US),$(Canada)))) as NewDate

Resident Table1;

Drop Table Table1;

Here I am getting an error in NewDate Line. Is there any solution in that line?

Regards,

Pawan