1 Reply Latest reply: Jan 26, 2012 9:26 AM by Jay Jethwa RSS

    Help with IntervalMatch

      Hi All - Hoping someone can help me out....I have an effective dated product costing table which I want to marry up with transactional sales data within QV

      I've managed to get the load script almost working, but having problems which I beleive is data volume related

       

      So I've got a QVD with the costs, which contains:

      ProductID

      Cost

      Start Date

      End Date

       

      This returns about 1.5m records and canont be limited any further.  the dates will also never overlap

       

      I then use a calander table with INTERVALMATCH to expand the costing table so it contains every possible date within the Start and End dates

      so end result is something like:

      ProductID

      Cost

      Start Date

      End Date

      ProdctID & Date     as FK KEY

       

      I can replicate the [FK KEY] on my sales side, so envison using a ApplyMap with the [FK KEY] to get the Cost value

      The calander table has 4 years of dates (about 1460records) and cannot be limited.  But the script always fails as its built a 350m+ dataset

       

      Is there a better way of doing something like this?  I suspect the problem is INTERVALMATCH

       

      My Script:

       

      //LOAD DATES THE COSTS WILL BE EXPANDED FOR

      DATES:

      LOAD [DATE-DAY_ID]          as DAY_ID

      FROM

      DATES.qvd

      (qvd);

       

       

      COSTS:

      LOAD

        PRODUCTID

        COST,

                          START_DATE,

                          END_DATE;   

      SELECT  SQL......;

       

      //RETAIN COSTS INTO A QVD FILE

      STORE COSTS INTO COSTS.QVD;

       

      //EXPAND COSTS FOR EACH PERIOD USING START-END DATES

      outer join IntervalMatch ( DAY_ID ) LOAD START_DATE, END_DATE resident COSTS;

       

      COSTS_DATES:

      LOAD                     DAY_ID & '-' & PRODUCTID      as [$DATE-PRODUCT],

                COST

      RESIDENT COSTS

      where IsNull(DAY_ID) = 0;

       

      //STORE COST DATA FOR EACH DATA/ITEM

      STORE COSTS_DATES INTO COSTS_DATES.QVD;

       

      drop table DATES;

       

       

      Suggestons welcome.

      Thanks, Jay