Next Working Day

[#1]

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.

[swuehl]

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.

[#3]

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

[Anonymous]

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.

[Clever_Anjos]

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;

[swuehl]

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;

[Clever_Anjos]

swuehl, you´re really 'the guy'