Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Connecting tables using a date range

Hi,

I have two tables

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

Client IDSale Date
1001/1/14
1012/2/14
1005/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 IDDocument Note Date
10020/12/13
1007/1/14
1001/7/14
10120/02/14

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

Any help would be greatly appreciated.

1 Reply
chrwolf64
Contributor III

Re: Connecting tables using a date range

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