topic Re: Incremental loads - update in QlikView

Incremental loads - update

Mon, 22 Oct 2012 06:45:12 GMT

I did Incremental load which work ok, the problem is

The UPDATE doesn’t work.

I have three column : Customer_id(key), name , address.

I “load” from DB table only new records (according date variable)

Then “Concatenate” to load from QVD file

And “store into” QVD file.

My question is :

I want that Changes made (update) in the DB table for Customer_id (key)

that already exist in the QVD file , will be modified in accordingly

For example Customer_id change his address.

Thank you

Re: Incremental loads - update

fosuzuki — Mon, 22 Oct 2012 23:22:05 GMT

In QV Reference Manual, page 401 (v11 SR2), there is a sample code for Insert And Update incremental load:

QV_Table:

SQL SELECT PrimaryKey, X, Y

FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate

LOAD PrimaryKey, X, Y

FROM File.QVD

WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

* The key here is to first load from the DB all the Inserted/Updated records since the last reload. Then, you concatenate the data from the QVD file, using the exists() function to only concatenate the records which PrimaryKey don't match.

Hope this helps you

Fernando

Re: Incremental loads - update

Tue, 23 Oct 2012 06:30:17 GMT

i undersatnd , i think the problem is that i dont have a "ModificationTime " column.

and it is probably can not be done without this column .

thank you

Re: Incremental loads - update

Tue, 14 May 2013 14:21:51 GMT

Hey Sharon,

Without a key that's differentiated, QlikView won't know what needs updating.

Most Databases have a last modified time, or last update time, are you sure that this field exists but you just haven't pulled it in yet?

If this doesn't exist, again, you just need a unique key for QlikView to understand what to update. You can create this by making a key of all three fields and having QlikView interrogate that.

So if your data looks like

Customer ID Customer Name Address

12345 Joe 123 Sesame St
12346 Sue 811 Pollard St

You can create keys by concatenating your fields together. So that you'd get something like:

Customer key

12345 - Joe - 123 Sesame St

Now if any of these fields change (specifically Customer Name or Address), QlikView knows to based on the updated key.

For instance if the address changed to 933 Brown St, the new field would be 12345 - Joe - Brown St, and now QlikView knows to update that address.

Re: Incremental loads - update

Tue, 14 May 2013 14:25:25 GMT

hi al,

i want to store value to backend like qvd or something.i am getting input from excel file.

Re: Incremental loads - update

Anonymous — Wed, 07 Oct 2015 11:04:12 GMT

Hi,

i tried the incremental load but i have a problem because qlikview skips the dates, i.e. it should load 17-18-19 April but it skips 18 April.

Date format is 17/04/2015 00.00.00

My script is the following:

table1:

LOAD ESITO,

SERVICE_ID,

ERROR_CODE,

MONTH,

WEEK,

DAY,

HOUR

FROM

(qvd);

LastUpdate:

load Max (DAY) as MaxDate

/*Max DAY=17Apr*/

Resident table1;

let LastUpdate = peek('MaxDate', 0,'LastUpdate');

ODBC CONNECT TO Report;

SQL SELECT *

FROM Report.dbo.Table

WHERE DAY >=$(LastUpdate);

New data are about 18-19Apr, so i expected 17-18-19 in the new file qvd, but Qlikview skips 18 April.

What's the problem?Thank you.

Re: Incremental loads - update

Thu, 27 Jul 2017 14:26:36 GMT

But this is where the Primary Key doesnt exist right?

So if I need to update a Customer, it is already existing, and so the key exists.

Even if it would concatenate the data, doesn't that mean the Old data and New data are now both in the final qvd?

If Customer has Address of 90, update with 100, but since it is concatenated, now he has 2 addresses