Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
adam1988
Contributor II
Contributor II

Double interval match?

Hi,

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

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

DATA:

CustomerLoanDate
A41000

1.1.2012

A410001.2.2012
A410001.3.2012
A410001.4.2012
A415001.5.2012
A415001.6.2012

Then I've got table with CUSTOMER_TYPE:

Customer Type Valid_fromValid_to
A4L11.1.2012
A4L21.4.2012

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

TypeInterest_rateValid_FromValid_to
L10,051.1.2012
L10,11.3.2012
L20,61.1.2012
L20,71.2.2012

and another for special customer rates

CustomerSpecial_Interest_rateValid_fromValid_to
A40,0115.1.201215.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...

6 Replies
hic
Former Employee
Former Employee

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

Not applicable

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

adam1988
Contributor II
Contributor II
Author

Thanks to both of you. It was very helpful!

Adam

adam1988
Contributor II
Contributor II
Author

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...

Not applicable

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
Contributor II
Contributor II
Author

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.