Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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