3 Replies Latest reply: Mar 2, 2012 8:01 AM by Stefan Wühl RSS

    How to generate Date?

    Adotey Kwame

      Hi all,

       

      As a newbie i'm really having challenges generating a simpe date(month & year). I have a sort of Date named Date purchased in the excel data and it has only three records i.e 26/07/2011, 29/09/2011 and 13/12/2011.

       

      How then can i generate a date useful enough for my analysis.

       

       

      Thanks you in advance

        • How to generate Date?
          Stefan Wühl

          I am not really sure what is useful enough for your analysis, do you mean that you need more than 3 dates?

          There are several samples here in the forum how to generate e.g. a master calendar, or how to fill up missing records between dates.

           

          If you just want to create a month and year field from your date field, you could use date / time functions like month() and year() or monthname():

           

          Set DateFormat = 'DD/MM/YYYY';

           

          LOAD [Date Purchased],

          Year([Date Purchased]) as Year,

          Month([Date Purchased]) as Month,

          Monthname([Date Purchased]) as MonthName

          INLINE [

          Date Purchased

          26/07/2011

          29/09/2011

          13/12/2011

          ];

            • How to generate Date?
              Adotey Kwame

              Hello Swuehl,

                               

              Thanks a million but then how can i fill the missing records between dates.Let's say generate from "01/01/2011 to 31/12/2011"

                • How to generate Date?
                  Stefan Wühl

                  If you want to use the concept of a master calendar, you could write some lines like:

                   

                  Set DateFormat = 'DD/MM/YYYY';

                   

                  LOAD [Date Purchased],

                  [Date Purchased] as Date

                  INLINE [

                  Date Purchased

                  26/07/2011

                  29/09/2011

                  13/12/2011

                  ];

                   

                  LOAD

                  Date,

                  Year(Date) as Year,

                  Month(Date) as Month,

                  Monthname(Date) as MonthName;

                  LOAD

                  date(makedate(2011)+recno()-1) as Date

                  AutoGenerate 365;

                   

                  The lines in bold are creating all the dates for the year 2011.

                   

                  To fill in missing records in your original table, you could also look e.g. into the QV cookbook, example titled "Fill values in a data range using previous values."

                   

                  You can download the cookbook from

                  http://robwunderlich.com/Download.html