Connecting tables using a date range

[#1]

Hi,

I have two tables

One with each client ID and the date a sale took place

Client ID	Sale Date
100	1/1/14
101	2/2/14
100	5/7/14

I would like to connect it to the following table of documents, based on a date range of 30 days before and 90 days after

I can do this in an expression, but cannot work out how to do in during LOAD

If(Document_Note_Date-Sale_Date>=-30 and Document_Note_Date-Sale_Date<=90,Sale_Date)

Client ID	Document Note Date
100	20/12/13
100	7/1/14
100	1/7/14
101	20/02/14

Then I can connect the tables using a combination of Client ID + Sale Date

Any help would be greatly appreciated.

[cwolf]

Hi George,

you can solve your problem with the Intervalmatch Function:

TAB1a:
LOAD
ClientID & '|'& SaleDate as %ID,
*,
SaleDate-30 as Start,
SaleDate+90 as End
Resident TAB1;


Drop Table TAB1;


left Join(TAB2)
IntervalMatch(DocNoteDate)
LOAD
Start,
End
Resident TAB1a;


Left Join(TAB2)
LOAD
ClientID,
Start,
End,
%ID
Resident TAB1a;


DROP Field ClientID From TAB2;
DROP Fields Start,End;

Regards

Christian