topic Re: Update QVD-file with only latest values from DB in QlikView

Update QVD-file with only latest values from DB

Fri, 30 Aug 2013 08:00:09 GMT

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

Re: Update QVD-file with only latest values from DB

MayilVahanan — Fri, 30 Aug 2013 08:05:24 GMT

Try with incremental load

Please refer this link or this one

Re: Update QVD-file with only latest values from DB

Fri, 30 Aug 2013 08:07:14 GMT

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

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

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

LOAD the data from the QVD only WHERE date < 2 days ago
LOAD data from database >= two days ago
SAVE QVD

Re: Update QVD-file with only latest values from DB

Fri, 30 Aug 2013 08:09:27 GMT

What you are looking for is incremental reload here some usefull post:

http://www.quickintelligence.co.uk/qlikview-incremental-load/

Qlikview Notes: Incremental Load using Qlikview Components

Re: Update QVD-file with only latest values from DB

Fri, 30 Aug 2013 09:09:12 GMT

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?

Re: Re: Update QVD-file with only latest values from DB

Fri, 30 Aug 2013 09:17:20 GMT

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

Re: Update QVD-file with only latest values from DB

techvarun — Fri, 30 Aug 2013 09:33:07 GMT

http://community.qlik.com/thread/80160

Re: Update QVD-file with only latest values from DB

techvarun — Fri, 30 Aug 2013 09:34:00 GMT

http://community.qlik.com/thread/80160

Re: Update QVD-file with only latest values from DB

Fri, 30 Aug 2013 09:44:19 GMT

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