Load only matching records (with match from other table)
I have a big dataset which stores price history.
crec1_ref as [KeyPart],
Date(Date#(Mid(9000000-num(crec1_date),2),'YYMMDD'), 'DD-MM-YYYY') as [CostDate],
if(creccost3 = 0,creccost1, creccost2) as [Costs];
SQL SELECT *
WHERE crec1_rec_type = 1
AND crec1_date < 7918769
AND crectotal_cost <> 0;
STORE [CostTemp] INTO "DATA_NEW/PartCosts.QVD";
DROP TABLE [CostTemp];
From this dataset i want to load the records which match with a table filled with stock data. Later I will join them and calculate the stock, but i already took care of that.
MonthEnd([StockDate]) as [CostDate],
Because i only need the price information of the KeyParts in the [Stock] table. Since the price database is huge, I dont want to load everything each time. It takes 40 minutes to load this, while I only need a small amount of data. What can I do to make this selection?
Re: Load only matching records (with match from other table)
Besides joining you could also use a mapping-table and matching the data with applymap. But this will not really improve the loading time. Here you need a incremental load what meant that only new data or changed data will loading/matching from the database. The previous data will load from a qvd. For examples see in the manual or in help.