Incremental loads - update

[#1]

Hi

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

[fosuzuki]

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

[#3]

HI

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

[#4]

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.

[#5]

hi al,

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

[Anonymous]

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.

[#7]

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