topic Re: Next Working Day in QlikView

Next Working Day

Tue, 01 Oct 2013 13:43:42 GMT

Hi Everyone.

I would like to find next business day(Working day), Which should exclude holidays and sunday get next previous available date.

If i am processing Saturady, I should get Monday as working day, if Monday is holiday then I should get Thuesday as working day. I hope I am clear.

I tried following code but could not get the result. I am not sure how to do it in Qlikview load script.

HolidayTAb:

load *

Inline [Holidate, HolidayName

07/04/2012, Indepence day

09/03/2012, labor day

12/25/2012, X'mas

];

let vHolidayLIst=peek('Holidate');

Input Date;

firstworkdate(Date+1, 1, $(vHolidayLIst)) as NextDay,

;

LOAD

Date(Date#(20120701,'YYYYMMDD')+RecNo(),'MM/DD/YYYY') AS Date

AUTOGENERATE Today()+30 - MakeDate(2012, 7, 1) + 1;

In this Case on 02/07/2012 is Monday, 03/07/2012 is Tueday so I want when I select 03/07/2012 then i will get next working Day it should be 05/07/2012

But I am getting 04/07/2012 . So any help would be appreciated.

Regards

Deepak

Any Help would be appreciated.

Re: Next Working Day

swuehl — Tue, 01 Oct 2013 14:17:03 GMT

I corrected a few things, including the use of LastWorkDate instead of FirstWorkDate function:

SET DateFormat='MM/DD/YYYY';

HolidayTAb:

load *, num(Holidate) as NumHolidate

Inline [Holidate, HolidayName

07/04/2012, Indepence day

09/03/2012, labor day

12/25/2012, X'mas

];

LIST:

LOAD concat(NumHolidate,', ') as HolidateList Resident HolidayTAb;

let vHolidayLIst=peek('HolidateList', 0, 'LIST');

LOAD Date,

lastworkdate(Date+1, 1, $(vHolidayLIst)) as NextDay

;

LOAD

Date(Date#('20120701','YYYYMMDD')+RecNo()-1,'MM/DD/YYYY') AS Date

AUTOGENERATE Today()+30 - MakeDate(2012, 7, 1) + 1;

This creates your expected result for 03/07/2012.

Using the First/LastWorkDate functions assumes working days Mo - Fri (Sa is not a working day), so if this matches your requirement, you can use something like above.

Re: Next Working Day

Tue, 01 Oct 2013 17:11:07 GMT

Hi swuehl

Thanks for Reply. But I do not want to exclude Saturday. I just want to exclude Sundays and National & Bank Holidays.

Can you Please tell me the other way to do the same ??

Thanks in advance !!

Regards

Deepak

Re: Next Working Day

Anonymous — Tue, 01 Oct 2013 19:29:26 GMT

In this case it all manual work, something like this (if I didn't messup the syntax):

if(weekday(today())=5,
// today is Saturday
if(index($(vHolidayList),date(today()+2))>0,
// Monday is Holiday
today()+3, // next workday is Tuesday
// Monday is workday
today()+2 // next workday is Monday
),
// today is not Saturday
if(index($(vHolidayList),date(today()+1))>0,
// tomorrow is Holiday
today()+2, // after tomorrow
// tomorrow is workday
today()+1 // tomorrow
)
) as NextWorkday

I'm assuming that no Holidays on Saturday/Sunday. It will be more complex with 2-day holidays.

Re: Re: Next Working Day

Clever_Anjos — Tue, 01 Oct 2013 20:22:38 GMT

Not so optimized, but I think it works

SET DateFormat='MM/DD/YYYY';

HolidayTab:

load *, num(Holidate) as NumHolidate

Inline [Holidate, HolidayName

07/04/2012, Indepence day

09/03/2012, labor day

12/25/2012, X'mas

];

CalendarTmp:

LOAD

Date,

(FlgHoliday or FlgSunday) as FlgNotWorkingDay;

LOAD

Date,

Exists('Holidate',Date) as FlgHoliday,

(WeekDay(Date) = 'dom') as FlgSunday;

LOAD

Date(Date#('20120701','YYYYMMDD')+RecNo()-1,'MM/DD/YYYY') AS Date

AUTOGENERATE Today()+30 - MakeDate(2012, 7, 1) + 1;

left Join(CalendarTmp)

LOAD

Date as NextWorkingDay

resident CalendarTmp

where not FlgNotWorkingDay;

Calendar:

NoConcatenate

LOAD

Date,

min(NextWorkingDay) as NextWorkingDay

Resident CalendarTmp

Where Date < NextWorkingDay

Group by Date;

DROP Table CalendarTmp;

Re: Next Working Day

swuehl — Tue, 01 Oct 2013 21:32:10 GMT

I think you can avoid the cross product and the group by, just using peek() function with calendar sorted reversly:

HolidayTab:

load *, num(Holidate) as NumHolidate

Inline [Holidate, HolidayName

07/04/2012, Indepence day

09/03/2012, labor day

12/25/2012, X'mas

];

CalendarTmp:

LOAD Date, FlgNotWorkingDay,

Date(if(peek(FlgNotWorkingDay), peek(NextWorkingDay), peek(Date))) as NextWorkingDay;

LOAD

Date,

(FlgHoliday or FlgSunday) as FlgNotWorkingDay;

LOAD

Date,

Exists('Holidate',Date) as FlgHoliday,

(WeekDay(Date) = 6) as FlgSunday;

LOAD

Date(Today()+30-Recno()+1) as Date

AUTOGENERATE Today()+30 - MakeDate(2012, 7, 1) + 1;

Re: Next Working Day

Clever_Anjos — Tue, 01 Oct 2013 21:46:04 GMT

swuehl, you´re really 'the guy'