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

[Rich-HHE]

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:

customer	offer_date	recent_purch
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

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

[vgutkovsky · Accepted solution]

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

[Rich-HHE]

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!