Double interval match?

[adam1988]

Hi,

I've got this problem. (example data in xls file)

There is data table with date, customers and their loans.

DATA:

Customer	Loan	Date
A4	1000	1.1.2012
A4	1000	1.2.2012
A4	1000	1.3.2012
A4	1000	1.4.2012
A4	1500	1.5.2012
A4	1500	1.6.2012

Then I've got table with CUSTOMER_TYPE:

Customer	Type	Valid_from	Valid_to
A4	L1	1.1.2012
A4	L2	1.4.2012

And then I have two tables of Loans rates. One for Customer TYPE_RATE

Type	Interest_rate	Valid_From	Valid_to
L1	0,05	1.1.2012
L1	0,1	1.3.2012
L2	0,6	1.1.2012
L2	0,7	1.2.2012

and another for special customer rates

Customer	Special_Interest_rate	Valid_from	Valid_to
A4	0,01	15.1.2012	15.2.2012

What I need  (I think) is to use interval match to join CUSTOMER_TYP on DATA to know what was the type of customer in specific date. Then i need to join TYPE_RATE on CUSTOMER_TYPE and SPECIAL_RATE  on DATA to know, what rate to use in specific date (special rate prefered, type_rate otherwise). Everything is considering Valid_from / Valid_to.

Let's say, that i solved the issue of missing valid_to by this:

IntervalMatch without an Ending Date (or max value) in match table

But I have no idea, why using two interval matches doesnt work or what am I doing wrong. HOW TO INTERVALMATCH TYPER_RATE ON CUSTOMER_TYPE?

My incomplete solution is in attachment. Thx for any advice...

[hic]

Check your data model (ctrl-T, Source Table View) and you will see that you have unwanted synthetic keys. Fix that.

Further, check IntervalMatch and Slowly Changing Dimensions where I have a double intervalmatch as an example.

HIC

[#3]

please see attached app and script... it may help you ...

[adam1988]

Thanks to both of you. It was very helpful!

Adam

[adam1988]

Hi again Nagaraj,

I went it through and I had found a problem.

Because of inner join I loose all data where customers don't have special customer rate. This script construction doesn't allow me to replace it with different kind of join because it would double some rows.

Any idea how to change the script? Meantime I solved the issue by generating "date" for every day for every row between interval dates. But I don't think it's neat solution...

[#6]

Only for special customer use outer joing for rest of them use inner join itself, attached the script and it is working fine for me.

[adam1988]

It doesnt work for me...still getting double rows for one date and customer. For example Customer A4, Date "5.1.2012" has two customer rates.