Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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