Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Update QVD-file with only latest values from DB

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];

8 Replies
MayilVahanan

HI

Try with incremental load

Please refer this link or this one

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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

  1. LOAD the data from the QVD
  2. Find out the last record (MAX(timestamp) and save that in a variable vTS
  3. LOAD the data from the database with WHERE timestamp > $(vTS) (with the same columns names so the data is appended to the same table)
  4. Save new QVD

If you want to accomplish exactly what you are saying, loading the last two days you can

  1. LOAD the data from the QVD only WHERE date < 2 days ago
  2. LOAD data from database >= two days ago
  3. SAVE QVD
Not applicable
Author

Not applicable
Author

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?

Not applicable
Author

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"; 

techvarun
Specialist II
Specialist II

techvarun
Specialist II
Specialist II

Not applicable
Author

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