4 Replies Latest reply: Nov 6, 2017 6:09 PM by John Jackson RSS

    How to Join/Link different Dates

    John Jackson

      Hi folks,

       

      I have a particular requirement. I want to link 2 tables based on dates.

      Table 1

      Field ADate PurchasedField B
      11/1/2017a
      21/2/2017b
      32/1/2017c
      42/28/2017d
      53/1/2017e
      64/1/2017f
      74/2/2017g

       

      Table 2

      Field CDate orderedField D
      1001/4/2017p
      2001/13/2017q
      3002/20/2017r
      4002/25/2017s
      5003/1/2017t
      6004/5/2017u
      7004/6/2017v

       

      how do I link table 2 so that Date ordered is one month behind from Date Purchase. meaning: I want records in Feb (Date Purchased) to link with Jan in (Date ordered). Likewise, when the rows are summed, Result should be:

       

      Date Purchased-MonthStartField AField C
      Jan 2017
      Feb 20177300
      Mar 20175700
      Apr 201713500

       

      It has been a while in the community. I am back. feels good to be back.

        • Re: How to Join/Link different Dates
          John Jackson

          ignore the Jan data for table 2 because the relevant data would have been december 2016, which is not there. so null value is fine for that particular row.

           

          Thanks,

          • Re: How to Join/Link different Dates
            Marco Wedel

            Hi,

             

            welcome back.


            One solution might be:

            QlikCommunity_Thread_280447_Pic1.JPG

             

            Table1:
            LOAD [Field A],
                [Date Purchased],
                MonthName([Date Purchased]) as MonthPurchased,
                [Field B]
            FROM [https://community.qlik.com/thread/280447] (html, codepage is 1252, embedded labels, table is @1);
            
            Table2:
            LOAD [Field C],
                [Date ordered],
                MonthName([Date ordered],1) as MonthPurchased,
                [Field D]
            FROM [https://community.qlik.com/thread/280447] (html, codepage is 1252, embedded labels, table is @2);
            

             

             

            hope this helps

             

            regards

             

            Marco

              • Re: How to Join/Link different Dates
                John Jackson

                Hi Marco Thank you for the quick reply. I will try the soln and post it back here.

                Regards.

                  • Re: How to Join/Link different Dates
                    John Jackson

                    Marco Thanks again for your help.

                    As always, the requirements in reality are a lil complex :-)

                    addtional reqs are Table 1 & 2 has 3 common fields Country, Region and Taxable. so when joining Date purchased with Date Ordered (one month behind)- how to implement your logic while linking these 3 common fields. I have created the new tables below.

                    Table 1:

                    Field ADate PurchasedCountryRegionTaxable
                    11/1/2017USAAY
                    21/2/2017USABN
                    31/3/2017USACN
                    42/1/2017INDAY
                    52/2/2017RUSBN
                    62/3/2017CHIBY
                    72/4/2017USACY

                     

                    Table 2:


                    Field CDate OrderedCountryRegionTaxable
                    10012/1/2016USAAY
                    20012/2/2016USABN
                    30012/3/2016USACN
                    4001/1/2017INDAY
                    5001/2/2017RUSBN
                    6001/3/2017CHIBY
                    7001/4/2017BRACY

                     

                    Result:

                    Date Purchased-MonthstartField AField C
                    Jan 20176600
                    Feb 2017221200

                     

                    when summing these 3 new fields also should match. For eg, I included all rows from Table 2 except last row where Country is Brazil.