Discussion Board for collaboration related to QlikView App Development.
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
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,
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
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!