topic Re: How to check the number of public holidays days found between 2 dates in QlikView

How to check the number of public holidays days found between 2 dates

Anonymous — Tue, 18 Nov 2014 08:46:45 GMT

I have 2 dates, date_In and date_Out. I also have a list of bank holidays appended in a list ('holiday1,holiday2,holiday3')

I would like to know if there is a way to calculate in the script how many public holidays there are between date_in and date_out.

Thanks

Hasnaa

Re: How to check the number of public holidays days found between 2 dates

tresB — Tue, 18 Nov 2014 08:56:32 GMT

Have you tried NetWorkDays()?

Re: How to check the number of public holidays days found between 2 dates

Tue, 18 Nov 2014 09:00:54 GMT

Hi Hasnaa,

If you are looking to do this in the script have a look at the networkdays function. If you have a list of holidays, you can plug this in to the function.

So something like:

(Networkdays([date_in],[date_out])

Networkdays([date_in],[date_out], [your list of holidays here]) ) As Holiday_counter

In the example, the first function gives you all working days between the range, then used again you can exclude your defined holiday list, the net of the two is the number of holidays that fall between the dates

hope that helps

Joe

Re: Re: How to check the number of public holidays days found between 2 dates

Anonymous — Tue, 18 Nov 2014 09:01:38 GMT

Networkdays will actually calculate the difference between the two days. I have tried and it is not even excluding the holodays. What I really need is to get only the number of holidays that falls between the two dates.

Thanks

Hasnaa

Re: Re: How to check the number of public holidays days found between 2 dates

Anonymous — Tue, 18 Nov 2014 09:07:50 GMT

Networkdays is not taking into consideration holidays

Re: How to check the number of public holidays days found between 2 dates

Tue, 18 Nov 2014 09:16:44 GMT

You need to add the optional parameter for holidays like I said

Re: How to check the number of public holidays days found between 2 dates

tresB — Tue, 18 Nov 2014 09:19:39 GMT

One way could be like, create multiple dates using SubField() like:

HDays:

Load

Date#(SubField(PurgeChar(HolidayDates, chr(39)), ','), 'MM/DD/YYYY') as Holidays

From <>;

Then use cross join this table with rest of the table. Then you would get all the holidays against each of the date combination(in and out). Then you just have to compare them with simple IF like:

Load

Count(If(InDate<=Holidays and outDate>=Holidays,1)) as Count

or may be you can try with intervalmatch() as well.

Re: How to check the number of public holidays days found between 2 dates

Tue, 18 Nov 2014 09:23:35 GMT

I should add, you'll want to put your holiday list into a variable and use that as the holiday optional parameter

Re: Re: How to check the number of public holidays days found between 2 dates

Anonymous — Tue, 18 Nov 2014 09:50:12 GMT

Thats what I did. However, the network days function is not taking into consideration the holidays. I used the following in the script:

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

Returns_Date:

LOAD _Id_Operator AS OPERATOR_CODE,

Return_ID,

Time_In,

Time_Out

FROM

(biff, embedded labels, table is [Sheet1$]);

tmpHoliday :

LOAD

OPERATOR_CODE,

if([WORKINGDAY]='N',Date([CALDATE])) as Date

FROM

$(v_QVD)$(v_qvd_calendar)(qvd);

tmpConcat :

LOAD

OPERATOR_CODE,

concat(chr(39) & Date & chr(39),',') as HolidayDates

RESIDENT

tmpHoliday

GROUP BY OPERATOR_CODE;

Join(tmpConcat)

LOAD

OPERATOR_CODE,

Return_ID,

Time_In,

Time_Out

Resident Returns_Date;

TEST:

LOAD *,

NetWorkDays(Time_In, Time_Out) as `TAT NOholiday`,

NetWorkDays(Time_In, Time_Out, HolidayDates) as `TAT holiday`

RESIDENT tmpConcat;

The Time_In and Time_Out are in the format 'MM/DD/YYYY hh:mm:ss'. The holidays are in the format 'MM/DD/YYYY'. Could this be the issue?

Kindly advise

Thanks

Hasnaa

Re: How to check the number of public holidays days found between 2 dates

Tue, 18 Nov 2014 10:14:15 GMT

Yes I would also date your time fields in the network days and see how that goes

Also is there a need to join in like that? Do the holiday dates differ per operator_code? If not I would load that list as a variable from the temp table and use that as the parameter, to save the need of the join.

Re: How to check the number of public holidays days found between 2 dates

Anonymous — Tue, 18 Nov 2014 10:43:02 GMT

The holidays are different for several countries, in this case operator. I even use date in the script but then networkdays does not work at all. It is not excluding holidays . please see image attached

Re: How to check the number of public holidays days found between 2 dates

Tue, 18 Nov 2014 13:10:12 GMT

hmm, not sure if network days will work for you in that case, with a shifting third parameter like that. Think the only way to use it in your case, would be to loop through each of the operator codes in turn, rather than a full load, that should work for you.

Re: Re: How to check the number of public holidays days found between 2 dates

Wed, 19 Nov 2014 14:00:34 GMT

been quite busy, but the attached might help you, not been able to test it, so a bit of blind coding, so forgive the errors!

hope that helps

Joe

Re: Re: How to check the number of public holidays days found between 2 dates

Anonymous — Thu, 20 Nov 2014 05:16:55 GMT

Thanks for you help. I will check it