Join Challenge ( Thinking Interval )

[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 

[racer25]

Any help on this would be much appreciated.

[marcus_sommer · Accepted solution]

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:

t0: load * inline [
KEY, %, date
M018, 15%, 01.07.2017
M018, 12%, 01.10.2017
M018, 15%, 31.12.2017
];

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.

- Marcus

[racer25]

Many thanks Marcus both for the solution and the knowledge share of why this works. I'll certainly be able to implement.