Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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

HI

Try with incremental load

Please refer this link or this one

Not applicable

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

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

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

Not applicable

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

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

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

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
Valued Contributor II

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

techvarun
Valued Contributor II

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

Not applicable

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

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

Community Browser