2 Replies Latest reply: Jun 7, 2011 12:13 PM by aebrigham RSS

    IntervalMatch Question

      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

          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