Help with IntervalMatch / Effective Dated records

[#1]

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

[disqr_rm]

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.

[stephencredmond]

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