Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thank you worked perfectly!