topic Re: Finding 2 max dates in two tables in QlikView

Finding 2 max dates in two tables

pauledrich — Thu, 21 Nov 2013 18:26:43 GMT

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

Re: Finding 2 max dates in two tables

ariel_klien — Thu, 21 Nov 2013 18:46:46 GMT

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.

Ariel

Re: Finding 2 max dates in two tables

pauledrich — Tue, 26 Nov 2013 09:55:33 GMT

Thank you worked perfectly!