6 Replies Latest reply: Oct 1, 2013 5:46 PM by Clever Anjos RSS

    Next Working Day

    Deepak Kumar

      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
          Stefan Wühl

          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
              Deepak Kumar

              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
                  Michael Solomovich

                  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

                      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
                          Stefan Wühl

                          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;