join tables based on max date

[#1]

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 ... ;

[#2 · Accepted solution]

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);

[#3]

This is great, thank you!