2 Replies Latest reply: Feb 8, 2012 3:54 AM by Stephen Redmond RSS

    Help with IntervalMatch / Effective Dated records

      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

        • Help with IntervalMatch / Effective Dated records
          Rakesh Mehta

          Try this code:

           

          COSTS:

          LOAD *,

               DAY_ID & '-' & PRODUCTID as [$DATE-PRODUCT];

          LOAD

            PRODUCTID,

            COST,

            (START_DATE + IterNo() - 1) as DAY_ID

          WHILE (START_DATE + IterNo()) <= END_DATE;  

          SELECT  SQL......;

           

          This should replace all your code. All you would then need is a STORE to store a QVD.

          Hope this helps.

          • Help with IntervalMatch / Effective Dated records
            Stephen Redmond

            Hi Jay,

             

            I have been playing with something similar.  My goal is to get the Product cost into my transactions table so, rather than try and expand it out, I am going to join it into the Transactions table with the Interval match, then I can drop the Product Cost table:

             

            ProductPrice:

            Load *

            From temp_ProductPrice.qvd (qvd);

             

             

            Outer Join (Orders)

            IntervalMatch(DateID, ProductID)

            Load

                      StartDate,

                      EndDate,

                      ProductID

            Resident ProductPrice;

             

             

            Left Join (Orders)

            Load

                      StartDate,

                      EndDate,

                      ProductID,

                      Price

            Resident ProductPrice;

             

             

            Drop Table ProductPrice;

            Drop Fields StartDate, EndDate;

             

             

            See how that works for you.

             

             

            Regards,

             

             

             

            Stephen