4 Replies Latest reply: Aug 29, 2011 5:43 AM by Aissam Boumejjane RSS

    Integrating Networkdays in MasterCalander

    Aissam Boumejjane

      Hi Guys,

       

      I'm quite new to qlikview so forgive me for my ignorance.

       

      I have created a MasterCalander and added the Networkdays function in it. By doing this I would like the weekends to automatically be removed. However I don't know if this is the right approch. The main goal is to automatically remove all the weekends between the departure date (DEP) field en arrival date (ARR) field.

       

      I tried something myself.

       

      This is my script.

       

      LET varMin              =     num('01-01-2011');

      LET varMax             =    num('31-12-2011');

      LET vWorkDays       =    NetWorkDays('01-01-2011', '31-12-2011'); //does not work

       

       

      Date:

      Load Date($(varMin), ($(vWorkDays)) + rowno() -1) as tempdate

      AutoGenerate ($(varMax) - $(varMin)) - $(vWorkDays) +1; //does not work

       

      Dates:

      LOAD

           Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as DEP,

           Trim(Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY')) as Date,

           Date(Date#([ARR/NFD],'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as ARR,

       

      From

      [xyz.csv]

       

      Calender:

      LOAD

      tempdate as Date,

      Year(tempdate) as Year,

      Month(tempdate) as Month,

      Week(tempdate) as Week,

      'Q' & Ceil(Month(tempdate) / 3) as Quarter

      Resident Date;

       

      Drop Table Date;

       

      Please help me, I'm stuck

        • Integrating Networkdays in MasterCalander
          Sunil Chauhan

          see the followin

           

          LET varMin              =     num('01-01-2011');

          LET varMax             =    num('31-12-2011');

          LET vWorkDays       =    NetWorkDays(makedate(2011,01,01), makedate(2011,12,31));

           

          Date:

          Load Date($(varMin), ($(vWorkDays)) + rowno() -1) as tempdate

          AutoGenerate (varMax - varMin) - vWorkDays +1;

           

          Dates:

          LOAD

               Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as DEP,

               Trim(Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY')) as Date,

               Date(Date#([ARR/NFD],'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as ARR,

           

          From

          [xyz.csv]

           

          Calender:

          LOAD

          tempdate as Date,

          Year(tempdate) as Year,

          Month(tempdate) as Month,

          Week(tempdate) as Week,

          'Q' & Ceil(Month(tempdate) / 3) as Quarter

          Resident Date;

          • Integrating Networkdays in MasterCalander
            Stefan Wühl

            I am not sure if you want to use networkdays for that - it gives the number of working days between two dates.

            I think you are better of using the weekday function, creating a flag for working day / weekand or using the function in a where statement during load.

             

            e,g,

            LET varMin         =    num(Date#('01-01-2011','DD-MM-YYYY'));

            LET varMax         =    num(Date#('31-12-2011','DD-MM-YYYY'));

            LET vWorkDays      =    NetWorkDays(varMin,varMax); //does work now

             

              

            Date:

             

            Load Date($(varMin) + recno() -1) as tempdate

            AutoGenerate ($(varMax) - $(varMin)) - $(vWorkDays) +1; //does work now, but what do you want to achieve?

            // I would rather use AutoGenerate ($(varMax) - $(varMin)) +1;

             

             

             

            Dates:

             

            LOAD

             

                 Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as DEP,

             

                 Trim(Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY')) as Date,

             

                 Date(Date#([ARR/NFD],'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY') as ARR,

             

             

             

            From

             

            [xyz.csv]

             

             

             

            Calender:

             

            LOAD

             

            tempdate as Date,

             

            Year(tempdate) as Year,

             

            Month(tempdate) as Month,

             

            Week(tempdate) as Week,

             

            'Q' & Ceil(Month(tempdate) / 3) as Quarter,

            //if(weekday(tempdate)<5,1,0) as workday,

            //text(weekday(tempdate)) as Weekday

             

            Resident Date where weekday(tempdate)<5 ;

             

            //this removes the weekend from your master calendar, if you want that? I would rather keep all days and use  a flag (see commented line above)

             

            Regards,

            Stefan

             

            P.S. I think instead of

            Trim(Date(Date#(DEP,'DD-MM-YYYY hh:mm'), 'DD-MM-YYYY')) as Date,

            you probably want

            Date(daystart(Date#(DEP,'DD-MM-YYYY hh:mm')), 'DD-MM-YYYY') as Date,