Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pauledrich
Contributor

Finding 2 max dates in two tables

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

1 Solution

Accepted Solutions
ariel_klien
Valued Contributor

Re: Finding 2 max dates in two tables

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

2 Replies
ariel_klien
Valued Contributor

Re: Finding 2 max dates in two tables

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
Contributor

Re: Finding 2 max dates in two tables

Thank you worked perfectly!


Community Browser