Join Question with Dates Between

[racer25]

Hi All,

Hope you are well.

Struggling where to start with one

I have this data ( small sample )

INSURER CODE	INSURER BRANCH	RISK CODE	INCOME DATE	PREMIUM	COMMISSION	ACTUAL RATE	DEFAULT RATE
M018	100	A001	01/01/2018	1000	100	10%	15%
M018	100	A001	01/11/2017	2000	360	18%	12%

And I am trying to populate the DEFAULT RATE from ICA_RATE below.

ICA_INSCODE	ICA_INSBRANCH	ICA INS_CODE	ICA RATE	ICA DATE
M018	100	A001	15%	01/07/2017
M018	100	A001	12%	01/10/2017
M018	100	A001	15%	31/12/2017

There can be many Insurer Codes and Risk Codes. The rate should be selected from Income Date in between the 2 dates or if nothing in between then today.

In my example it picked 15% as it was the last update and 12% as after 01/10/2017 but before the next updated date.

I know as much to think of Interval Match but little beyond that.

If anyone could help me out on this it would be very much appreciated.

Thanks

Rob 

[Miguel_Angel_Baeyens]

See examples
https://community.qlik.com/t5/QlikView-App-Development/help-using-a-BETWEEN-join-on-tables-from-diff...
and
https://community.qlik.com/t5/QlikView-App-Development/max-1-match-in-interval-match-function/td-p/1...

and of course https://community.qlik.com/t5/Qlik-Design-Blog/Slowly-Changing-Dimensions/ba-p/1464187 and linked documents.