2 Replies Latest reply: Dec 6, 2012 3:48 PM by rich calligan RSS

    When joining two tables, want to reference the target table's field

    rich calligan

      I have two tables: Purchases which is a list of purchases made by a customer and the date the purchase was made and Offers which is a list of offers made to the customers and the date the offer was made. I need to find, for each offer, what the most recent purchase date was that customer at the time of the offer. Below is my inline load script to illustrate what I need.

       

      purchases: LOAD * INLINE [

          customer, purchase_date

          A, 1/1/2012

          A, 9/1/2012

          B, 5/1/2012

      ];

       

      offers: LOAD * INLINE [

          customer, offer_date

          A, 3/15/2012

          A, 11/15/2012

          B, 1/15/2012

          B, 10/15/2012

      ];

       

      LEFT JOIN (offers) LOAD customer, max(purchase_date) as recent_purch RESIDENT purchases WHERE purchase_date<offer_date GROUP BY customer;

       

      DROP TABLE purchases;

       

      In the end I need the following:

       

      customeroffer_daterecent_purch
      A3/15/20121/1/2012
      A11/15/20129/1/2012
      B1/15/2012-
      B10/15/20125/1/2012

       

      I cannot reference the "offer_date" on the target table (I don't believe it's possible in either QlikView or SQL). How can I accomplish this? One way I tried was to loop through every offer date, set a variable to the current offer date, and query the purchases table for every record for each offer date. This takes way too long as I have over 1 million purchase records and 40 million offer records.

       

      Any help is greatly appreciated.

       

      Thanks!

      Rich Calligan

        • Re: When joining two tables, want to reference the target table's field
          Vlad Gutkovsky

          Rich,

           

          Starting with just your inline tables, this code should work:

           

          LEFT
           JOIN (offers) LOAD * RESIDENT purchases;
          
          
          
          data_final_temp:
          
          LOAD
          
           customer,
          
           offer_date,
          
           date(max(if(purchase_date<offer_date,purchase_date))) as MostRecentPurchase
          
          RESIDENT offers
          
          GROUP BY customer,offer_date
          
          ;
          
          
          
          DROP TABLES offers,purchases; 
          
          

           

          Regard,

          Vlad

            • Re: When joining two tables, want to reference the target table's field
              rich calligan

              Thank you! For some reason it took me a while to see why this works. I will explain it here for others as well as myself (the best way to learn something is to teach it).

               

              When doing a left join of purchases onto offers, the resulting offers table is as follows:

               

              customer purchase_date offer_date
              A 1/1/2012 3/15/2012
              A 9/1/2012 3/15/2012
              A 1/1/2012 11/15/2012
              A 9/1/2012 11/15/2012
              B 5/1/2012 1/15/2012
              B 5/1/2012 10/15/2012

               

              Then, you are querying the resulting offers table for the highest value (max) of offer date for each customer and purchase date (group by), which results in the desired result.

               

              customer offer_date MostRecentPurchase
              A 3/15/2012 1/1/2012
              A 11/15/2012 9/1/2012
              B 1/15/2012
              B 10/15/2012 5/1/2012

               

              Thanks for your help!