The two fields are in the same Fact Table with different records.And now i would want to put all the records in "Date Purchased" and "Waranty Expired Date" fields together such that they have a common Month,Qaurter and different Years.
LOAD Date ([Date Purchased])as Date,
[Waranty Expiry Date]
Clearly as depicted the Date list box ends with the year 2011 and the Waranty Expiry Date year ends in 2015.
Now i need the Date to read from 2011 to 2015 with common Month and Quarter.
One way to do this is to use a master calendar and a link table.
A master calendar is a table that holds all dates in your required date range (2011 to 2012) and all the derived calendar fields (Month, Year, Week, Quarter, Quarter, etc.).
The link table will just link your different date fields [Date Purchased] and [Waranty Expiry Date] to the calendar.
The link table key to your original table is the record ID or order ID, the link to the calendar the date.
Please have a look at John's sample on how to generate that table structure.
You don't need to have two or three original tables, your original date fields could reside in one table.
Hope this helps,
Have you got the chance to look into John's sample or are you working on a personal edition?
Here is a slightly modified and simplified version of his script:
LOAD recno() as ID, * INLINE [
Date Purchased,Waranty Expiry Date, Value
2010-7-10, 2011-5-10, 10
2010-8-10, 2011-5-12, 20
2010-5-10, 2011-6-15, 40
,[Date Purchased] as Date
,'Purchased' as DateType
,[Waranty Expiry Date] as Date
,'WarantyEx' as DateType
,date(monthstart(Date),'MMM YYYY') as Month
LOAD date(makedate(2010)+recno()-1) as Date
if you execute the script and look at the table view (CTRL-T), you'll notice that the Link table will be linked by ID and Date to the other two tables, that's what I was talking about.