9 Replies Latest reply: Jul 26, 2012 7:59 AM by bogd.cristescu RSS

    Autogenerate date?

      Hello,

       

      I have a table with dates (date_debut, date_fin) and I am trying to load a calendar into QV in order to get a better overview of the ocupied periods in certain months. Would it be possible to generate something that can allow me to select the Year, Month, Day and afterwards match it with the period between the two columns in the table? I read something about auto generate, but couldn't quite get the hang of it. Hope my questions make sense, I'm new at this and I would really appreciate your help.

       

      This is how the table looks like when i load it:

       

       

      LOAD company_id,

           weekends,

           periode_id,

           projet_id,

           user_id,

           specialitate_id,

           sprojet_id,

           sprojet_specialitate_id,

           locatie,

           date_debut,

           date_fin,

           duree,

           notes

       

      Thank you in advance,

       

      Bogdan

        • Re: Autogenerate date?
          Martina Brenner

          Hi Bogdan,

           

          look at the attached file

           

          Greetings from Munich

           

          Martina

            • Re: Autogenerate date?

              Hello Martina,

               

              Thank you for the quik reply. Unfortunately I'm currently using the Personal Edition of QV, so I cannot open that file. Would it be possible for you to paste it in a commnt?

               

              Thank you so much,

               

              Bogdan

                • Re: Autogenerate date?
                  Martina Brenner

                  Hi Bogdan,

                   

                  no problem look here:

                   

                  //******************* Table Calender *******************

                  Let varMinDate = Num(Makedate(2008,1,1));
                  Let varMaxDate = Num(Makedate(Year(today())+1,12,31));

                  Datefield:
                  LOAD date($(varMinDate)+IterNo()-1) AS Datefield
                  AUTOGENERATE (1)
                  WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

                  Calender:
                  LOAD
                  Datefield AS Date,
                  Year(Datefield) AS Year,
                  Month(Datefield) as Month,
                  Dual(Date((Datefield),'MMMM'),Num(Month(Datefield))) as Months,
                  Floor(Monthstart(Datefield)) as Monthstart,
                  Week(Datefield) AS Week,
                  Weekday(Datefield) AS Weekday,
                  Day(Datefield) AS Day,
                  'Q ' & Num(Ceil(Month(Datefield)/3),'(ROM)0') AS Quarter,
                  Monthname(Datefield) AS RollMonth,
                  Num(Monthname(Datefield)) AS NumRollmontj,
                  (Num(Floor(Monthend(Datefield)))-Num(Floor(Monthstart(Datefield)))+1) as NumberDays,
                  If(Num(Weekday(Datefield))<5,1,0) as WorkingDay,
                  YearToDate(Datefield,0) as YTDActualYear,
                  YearToDate(Datefield,-1) as YTD1PreYear,
                  YearToDate(Datefield,-2) as YTD2PreYear,
                  YearToDate(Datefield,1) as YTDFollowingYear

                  RESIDENT Datefield;

                  //Delete temp table
                  DROP TABLE Datefield;

                  //Clean Up Variables

                  SET varMinDate = ;
                  SET varMaxDate = ;

                  //*************************************Ende Script**********************************************