Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Rich-HHE
Contributor III
Contributor III

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

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

1 Solution

Accepted Solutions
vgutkovsky
Master II
Master II

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

View solution in original post

2 Replies
vgutkovsky
Master II
Master II

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
Contributor III
Contributor III
Author

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!