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.
You need to create a from - to from your ICA DATE and then you could populate it to a continuous date with an intervalmatch-logic (personally I prefer the use of while-loops because this "manually" way is more flexible). The result could you then join or map to your fact-table. Here a simplified example what is meant:
t1: mapping load KEY & '|' & date(from + iterno() -1) as KeyDate, % while from + iterno() -1 <= to; load KEY, date as from, if(KEY = previous(KEY), date(previous(date)-1), today()) as to, % resident t0 order by KEY, date desc;
drop tables t0;
Important by such an approach is the proper sorting within a resident load.
You need to create a from - to from your ICA DATE and then you could populate it to a continuous date with an intervalmatch-logic (personally I prefer the use of while-loops because this "manually" way is more flexible). The result could you then join or map to your fact-table. Here a simplified example what is meant:
t1: mapping load KEY & '|' & date(from + iterno() -1) as KeyDate, % while from + iterno() -1 <= to; load KEY, date as from, if(KEY = previous(KEY), date(previous(date)-1), today()) as to, % resident t0 order by KEY, date desc;
drop tables t0;
Important by such an approach is the proper sorting within a resident load.
