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
