Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Next Working Day

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.

Tags (1)
6 Replies
MVP
MVP

Re: Next Working Day

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.

Not applicable

Re: Next Working Day

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

mov
Esteemed Contributor III

Re: Next Working Day

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.

Employee
Employee

Re: Re: Next Working Day

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;

MVP
MVP

Re: Next Working Day

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;

Employee
Employee

Re: Next Working Day

swuehl, you´re really 'the guy'

Community Browser