topic Re: Exclude Weekends and Public holidays using one datefield in QlikView

Exclude Weekends and Public holidays using one datefield

Wed, 25 Nov 2020 16:16:04 GMT

Good day,

The question I asked earlier on was not so clear. Now I am simplifying it, I have a date field called LeaveDate and it contains Working days from Monday to Friday, Weekends and Holiday. So in my country if a holiday falls on a Sunday, then following day(i.e Monday) will be counted as a holiday. So in my attached spreadsheet, I have highlited the holidays and weekends in Yellow. So what I would like to exclude weekends and public holidays.

hope my question is clear.

Thanks

Re: Exclude Weekends and Public holidays using one datefield

sunny_talwar — Wed, 28 Dec 2016 11:46:13 GMT

Exclude them how? Exclude them from loading? Also, do you have this second column which specify the word 'Holiday' or is that something you have to derive from another place?

Re: Exclude Weekends and Public holidays using one datefield

Wed, 28 Dec 2016 11:50:15 GMT

Hi Sunny,

May be have an indicator created to identify it's a holiday/weekend then I will use the indicator in my expression to exclude them in my calculation.

Re: Exclude Weekends and Public holidays using one datefield

sunny_talwar — Wed, 28 Dec 2016 11:52:06 GMT

Weekend indicator is easy, how do we know if a certain date is a holiday? I mean 12/16/2016 is a holiday in your country, where do you get that information from?

Re: Exclude Weekends and Public holidays using one datefield

Wed, 28 Dec 2016 11:52:22 GMT

I actually have Public holiday spreadsheet which has holidays.

PFA

Re: Exclude Weekends and Public holidays using one datefield

raajaswin — Wed, 28 Dec 2016 11:52:54 GMT

you can generate a consolidate holiday list then by using where not exist you can remove the holidays

Re: Exclude Weekends and Public holidays using one datefield

sunny_talwar — Wed, 28 Dec 2016 11:53:32 GMT

I don't see the attachment, can you may be reattach?

Re: Exclude Weekends and Public holidays using one datefield

Wed, 28 Dec 2016 11:55:12 GMT

Re: Exclude Weekends and Public holidays using one datefield

sunny_talwar — Wed, 28 Dec 2016 12:00:46 GMT

Since 25th Dec was a Sunday, you get extra holiday on 27th? because 26th was a Holiday also?

Re: Exclude Weekends and Public holidays using one datefield

raajaswin — Wed, 28 Dec 2016 12:04:31 GMT

here holiday has been generated form 1/1/2015 to 1/1/2017 and your public holiday is added to that

Re: Exclude Weekends and Public holidays using one datefield

Wed, 28 Dec 2016 12:04:36 GMT

Yes it was just given to us by the president, but under normal circumstances it's not a holiday. The holidays are 25th and 26th

Re: Exclude Weekends and Public holidays using one datefield

raajaswin — Wed, 28 Dec 2016 12:06:26 GMT

LET vDatMi = Num(MakeDate(2015,1,1));

LET vDatMa = Num(MakeDate(2017,1,1));

CAL1:

LOAD

Date($(vDatMi) + IterNo()) AS ALL_DATE

AUTOGENERATE 1

WHILE $(vDatMi)+IterNo() <= $(vDatMa);

HOLIDAY_WEEKEND:

LOAD

if(match(weekday(ALL_DATE),'Sat','Sun'),ALL_DATE) as Holidays

Resident CAL1;

Drop Table CAL1;

Concatenate

PUBLIC_HOLIDAY_LIST:

LOAD Holiday as Holidays

FROM

[Public Holidays.xlsx]

(ooxml, embedded labels, table is Sheet1);

LEAVE:

LOAD LeaveDate as Working_day

FROM

[Leave Data.xlsx]

(ooxml, embedded labels, table is Sheet1) where not Exists(Holidays,LeaveDate);

Re: Exclude Weekends and Public holidays using one datefield

Wed, 28 Dec 2016 12:14:19 GMT

Thank you Aswin, this is perfect.

Re: Exclude Weekends and Public holidays using one datefield

sunny_talwar — Wed, 28 Dec 2016 12:15:25 GMT

Check this out

Script

Table:

LOAD Holiday,

NewHoliday,

Date(If(NewHoliday = Peek(NewHoliday), NewHoliday + 1, NewHoliday)) as NewHoliday2;

LOAD Holiday,

Date(If(WeekDay(Holiday) = 'Sat', Holiday + 2, If(WeekDay(Holiday) = 'Sun', Holiday + 1, Holiday))) as NewHoliday;

LOAD * INLINE [

Holiday

1/1/2015

3/4/2015

3/21/2015

3/22/2015

4/27/2015

5/1/2015

6/4/2015

6/16/2015

8/9/2015

8/10/2015

9/24/2015

12/16/2015

12/25/2015

12/26/2015

12/27/2015

1/1/2016

3/21/2016

3/25/2016

3/28/2016

4/27/2016

5/1/2016

5/2/2016

6/16/2016

8/9/2016

9/24/2016

9/25/2016

12/16/2016

12/25/2016

12/26/2016

];

Calendar:

LOAD Date,

Month(Date) as Month,

Year(Date) as Year,

MonthName(Date) as MonthYear,

If(Match(WeekDay(Date), 'Sat', 'Sun'), 'Weekend',

If(Exists(NewHoliday2, Date), 'Holiday')) as Flag1;

LOAD Date(MakeDate(2014, 12, 31) + IterNo()) as Date

AutoGenerate 1

While MakeDate(2014, 12, 31) + IterNo() <= MakeDate(2017, 1, 1);

DROP Table Table;

Re: Exclude Weekends and Public holidays using one datefield

sunny_talwar — Wed, 28 Dec 2016 12:17:03 GMT

Is this perfect? Didn't you just say that 12/27/2016 should be a holiday?

I guess I might not have understood your requirement

Re: Exclude Weekends and Public holidays using one datefield

raajaswin — Wed, 28 Dec 2016 12:18:47 GMT

Sunny,

The public holiday list has been attached by Moses in a different thread.

Re: Exclude Weekends and Public holidays using one datefield

sunny_talwar — Wed, 28 Dec 2016 12:20:09 GMT

Oh I see.... but I am still confused about if 12/27/2016 is a holiday or not

Re: Exclude Weekends and Public holidays using one datefield

raajaswin — Wed, 28 Dec 2016 12:21:59 GMT

Exclude Weekends and Public Holiday

It is not a holiday because it is not provided in the public holiday list

Re: Exclude Weekends and Public holidays using one datefield

Wed, 28 Dec 2016 12:25:27 GMT

Hi Sunny,

I have included 27th manually on my Public holiday field for only this year but as indicated earlier on that under normal circumstances it's not a public holiday

Re: Exclude Weekends and Public holidays using one datefield

sunny_talwar — Wed, 28 Dec 2016 12:26:03 GMT

My bad, you are right, I was looking at 2015