Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

adam1988
Not applicable

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

Tags (1)
6 Replies
Henric_Cronström
Not applicable

Re: Double interval match?

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

Re: Double interval match?

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

adam1988
Not applicable

Re: Double interval match?

Thanks to both of you. It was very helpful!

Adam

adam1988
Not applicable

Re: Double interval match?

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

Re: Re: Double interval match?

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
Not applicable

Re: Double interval match?

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.