How to merge two date fields?

[Anonymous]

Hello,

       I'm hoping to merge two date Fields namley "Date Purchased" and "Waranty Expired Date". I managed to do this Date([Date Purchased]) as Date, and now i want to merge "Date Purchased" and "Waranty Expired Date" as one common date.

Thanks

[swuehl]

What do you mean with "merge"? Where are the two fields "Date Purchased" and "Waranty Expired Date" located (in which tables)? And what is your expected result?

I think some lines showing the data Input and requested Output might help me to understand your issue.

[Anonymous]

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.

Thanks

[swuehl]

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.

http://community.qlik.com/message/118471

Hope this helps,

Stefan

[Anonymous]

Hi Stefan !!

Kindly explain this

"The link table key to your original table is the record ID or order ID, the link to the calendar  the date."

Thanks

[swuehl]

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:

SET DateFormat='YYYY-M-D';

YOURTABLE:

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

];

Link:

LOAD

ID

,[Date Purchased] as Date

,'Purchased' as DateType

RESIDENT YOURTABLE

;

CONCATENATE (Link)

LOAD

ID

,[Waranty Expiry Date] as Date

,'WarantyEx' as DateType

RESIDENT YOURTABLE

;

Calendar:

LOAD *

,date(monthstart(Date),'MMM YYYY') as Month

,year(Date)

;

LOAD date(makedate(2010)+recno()-1) as Date

AUTOGENERATE 720

;

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.