5 Replies Latest reply: Mar 8, 2012 6:33 AM by Stefan Wühl RSS

    How to merge two date fields?

    Adotey Kwame

      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

        • How to merge two date fields?
          Stefan Wühl

          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.

            • Re: How to merge two date fields?
              Adotey Kwame

              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.

               

              Capture.GIF

               

              Now i need the Date to read from 2011 to 2015 with common Month and Quarter.

               

              Thanks

                • How to merge two date fields?
                  Stefan Wühl

                  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

                    • How to merge two date fields?
                      Adotey Kwame

                      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

                        • How to merge two date fields?
                          Stefan Wühl

                          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.