Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a database with price records. Every day, lets say 20 records are added. The database itself is over 3 million records big.
I dont want to reload these costs every time I reload my script. Is it possible to reload only the records of the past 2 days? or just the new ones?
My current code looks like this:
[CostTemp]:
LOAD
crec1_ref as [KeyPart],
Date(Date#(Mid(9000000-num(crec1_date),2),'YYMMDD'), 'DD-MM-YYYY') as [CostDate],
if(creccost3 = 0,creccost1, creccost2) as [BewerkingsKosten],
if(creccost3 = 0,creccost2, creccost3) as [MateriaalKosten],
if(creccost3 = 0,0, creccost1) as [MachineKosten],
crectotal_cost as [TotaleKosten];
SQL SELECT
crec1_co_site,
crec1_rec_type,
crec1_ref,
crec1_date,
creccost1,
creccost2,
creccost3,
crectotal_cost
FROM CREC_DATA
WHERE crec1_co_site = '$(Company)'
AND crec1_rec_type = 1
AND crec1_date < 7918769
AND crectotal_cost <> 0;
STORE [CostTemp] INTO "DATA_NEW/PartCosts.QVD";
DROP TABLE [CostTemp];
Yes you can, assuming there is some value you can use to select only the new records (e.g. a timestamp).
What you need to do is
If you want to accomplish exactly what you are saying, loading the last two days you can
What you are looking for is incremental reload here some usefull post:
http://www.quickintelligence.co.uk/qlikview-incremental-load/
Thank you
I want to use your first solution.
1. How can I get this value in a variable? Because the 'ID' is the date, but I usually have more prices on one day.
2. Could you explain me how I can merge the 2 QVD files?
Should be something like this (haven't tested it, so the might be a typo or two):
//read the QVD file
CostTemp:
LOAD
[KeyPart],
[CostDate],
[BewerkingsKosten],
[MateriaalKosten],
[MachineKosten],
[TotaleKosten];
FROM [DATA_NEW/PartCosts.QVD] (qvd);
//find the last date
MinMax:
Load Max(CostDate) As maxdate
Resident MyTable;
Let vMaxDate = Peek('maxdate', 0, 'MinMax');
//format the date for use in SQL statement
Let vMaxDateFormatted = Date(vMaxDate, 'YYYY-MM-DD');
//load only new records
LOAD
crec1_ref as [KeyPart],
Date(Date#(Mid(9000000-num(crec1_date),2),'YYMMDD'), 'DD-MM-YYYY') as [CostDate],
if(creccost3 = 0,creccost1, creccost2) as [BewerkingsKosten],
if(creccost3 = 0,creccost2, creccost3) as [MateriaalKosten],
if(creccost3 = 0,0, creccost1) as [MachineKosten],
crectotal_cost as [TotaleKosten];
SQL SELECT
crec1_co_site,
crec1_rec_type,
crec1_ref,
crec1_date,
creccost1,
creccost2,
creccost3,
crectotal_cost
FROM CREC_DATA
WHERE crec1_co_site = '$(Company)'
AND crec1_rec_type = 1
AND crec1_date < 7918769
AND crectotal_cost <> 0
AND Date(Date#(Mid(9000000-num(crec1_date),2),'YYMMDD'), 'DD-MM-YYYY') > '$(vMaxDateFormatted)';
STORE [CostTemp] INTO "DATA_NEW/PartCosts.QVD";
Thanks alot! I guess I will have to reload twice, to make sure I have all the data?
Not that it would be a problem. Im creating the 4+ mil qvd right now, takes at least 40 minutes