IntervalMatch Question

[#1]

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

[#2]

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

[#3]

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