How to generate Date?

[Anonymous]

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

[swuehl · Accepted solution]

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

];

[Anonymous]

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"

[swuehl]

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