Finding 2 max dates in two tables

[pauledrich]

Hi

I have two  transaction tables with customer IDs, One table is Buy & the other is Sell Field names are CustomerBuyId & CustomerSellId both tables have the Field Transactiondate .

I would like to determine if a customer who buys also returns to sell at a later date - there maybe a really simple way to script this as a flag - I have tried:-

LOAD DISTINCT
CustomerSellID,
Max(TransactionDate)as LastSellDate
RESIDENT Sells
GROUP BY CustomerSellID;

JOIN (Buys) LOAD DISTINCT
CustomerBuyID, 
Max(TransactionDate)
RESIDENT tmp;
DROP TABLE tmp;
LEFT JOIN (Buys) LOAD DISTINCT
LastSelldate,
CustomerBuyId, 
Max(TransactionDate),
if(CustomerBuyID=CustomerSellID,'Yes','No') as ReturningCustomer
RESIDENT Buys;

Field not found errors happen on step through - any help or alternative solutions would be a great help.

Many thanks

[ariel_klien · Accepted solution]

HI,

Try this:

Temp:

LOAD DISTINCT
CustomerBuyId as CustomerID,
Max(TransactionDate) as LastBuyDate
RESIDENT Buys
GROUP BY CustomerBuyID ;

Left Join

LOAD DISTINCT
CustomerSellID as CustomerID,
Max(TransactionDate)as LastSellDate
RESIDENT Sells
GROUP BY CustomerSellID;

LEFT JOIN (Buys)

LOAD CustomerID as CustomerBuyId,
          if(LastSellDate-LastBuyDate>0,'Yes','No') as ReturningCustomer
RESIDENT Temp;

drop table Temp;

hope this will help you.

BR

Ariel

[pauledrich]

Thank you worked perfectly!