topic IntervalMatch Question in QlikView

IntervalMatch Question

Tue, 07 Jun 2011 14:04:25 GMT

Hi There,

I have been dealing with a difficult IntervalMatch issue regarding a large set of records returned. I will try to explain the best I can. I have created a RESIDENT table called RateMaster that contains a daily record for each "Prime Rate" ie...

RateMaster

Date, Index, Rate

2011/01/01, RB, 2.25

2011/01/02, RB, 2.25

2011/01/03, RB, 2.25

2011/01/04, RB, 2.50

2011/01/05, RB, 2.50

2011/01/06, RB, 2.50

2011/01/07, RB, 2.50

2011/01/08, RB, 2.50

...

2011/02/01, RB, 2.50

2011/02/02, RB, 2.50

2011/02/03, RB, 2.50

2011/02/04, RB, 2.50

2011/02/05, RB, 2.50

...

I have a Loan record that has a start and end date.

LoanMaster

Loan, StartDate, EndDate

123456, 2011/01/03, 2011/02/02

I am using IntervalMatch to join all the interval dates between startdate and enddate ie.

LEFT JOIN (RateMaster)

IntervalMatch(Date) LOAD StartDate, EndDate RESIDENT LoanMaster;

Everything works, accept the speed at which this works. My goal is to calculate an average primerate between startdate and enddate for each loan in LoanMaster. I get 31 million plus rows and it takes like 30 mins to load. I am looking for advice on how to do this more effeciently.

I have attached a sample.qvw the tabs that I need help with are PrimeRate and AvgPrimeRate

IntervalMatch Question

Tue, 07 Jun 2011 15:26:40 GMT

Hi, I have just seen your application and I can say your code looks fine. However, I think your reload time can be improved by splitting your development into two layers:

-First, create a qvw to extract the data from sources and store it, into qvds.

-Second, create a qvw to load your qvds and do all needed transformations to complete your data model.

This way, you will avoid extracting information from the datasources on each application reload.

Regards

IntervalMatch Question

Tue, 07 Jun 2011 16:13:15 GMT

Hi There,

Thank-you for the quick response.

I was able to resolve my issue by changing the LEFT JOIN just before the IntervalMatch to an INNER JOIN...

I truly wish there was a better way to do RANGE joins...