Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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
];
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"
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