topic join tables based on max date in QlikView

join tables based on max date

Tue, 08 Feb 2011 20:36:53 GMT

I have two tables, Purchases and Electronic that have the same fields. I want to take max weekend from Purchases tables, and then load data from Electronic table which is posted after max weekend from Purchases table.

So if max weekend in Purchases is 01/16/2011, then I want to load just data from Electronic that has been posted after 01/16/2011.

Purchases:
LOAD
Store,
GL,
(Amount)*-1 as Amount,
Weekend(postDate) as Weekend;

SQL SELECT ... ;

Electronic:
LOAD
GL,
Weekend(PostDate) as Weekend,
Store,
PurchasesElectronic*-1 as Amount

SQL SELECT ... ;

join tables based on max date

Tue, 08 Feb 2011 21:39:18 GMT

Hi,

try something like this:

Purchases:
LOAD
Store,
GL,
(Amount)*-1 as Amount,
Weekend(postDate) as Weekend;

SQL SELECT ...

ORDER BY postDate ASC;

LET varMaxWeekend = num(peek('Weekend',-1,'Purchases'));

Electronic:
LOAD
GL,
Weekend(PostDate) as Weekend,
Store,
PurchasesElectronic*-1 as Amount

SQL SELECT ...

where Weekend(PostDate) > $(varMaxWeekend);

join tables based on max date

Tue, 08 Feb 2011 22:11:36 GMT

This is great, thank you!