Discussion Board for collaboration on QlikView Scripting.
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 LastSellDateRESIDENT SellsGROUP 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.
Go to Solution.
LOAD DISTINCTCustomerBuyId as CustomerID, Max(TransactionDate) as LastBuyDateRESIDENT BuysGROUP BY CustomerBuyID ;
LOAD DISTINCTCustomerSellID as CustomerID,Max(TransactionDate)as LastSellDateRESIDENT SellsGROUP 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.
Thank you worked perfectly!