topic Re: Incremental Load- Inser & Update: Error while using multiple fields as key in concatenation in App Development

Incremental Load- Inser & Update: Error while using multiple fields as key in concatenation

Qlik_Enthu — Tue, 15 Jun 2021 08:14:44 GMT

Hi All,

I am following the below method for Insert n Update incremental Load:

QV_Table:

SQL SELECT %Key, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD %Key, X, Y FROM File.QVD

WHERE NOT Exists(%KEY);

STORE QV_Table INTO File.QVD;

i am getting the error:

The following error occurred:

Field '%KEY' not found

%KEY is concatenation of few fields that is loaded into the QVD.

Kindly help what is the mistake i made here..

Also, please clarify that Not Exist will update the particular record with new field value (like last updated Date-Time) or insert entire new record with updated values along with old record?

Re: Incremental Load- Inser & Update: Error while using multiple fields as key in concatenation

JuanGerardo — Tue, 15 Jun 2021 08:48:27 GMT

Hello, Qlik Sense is case sensitive, so %KEY is not the same as %Key. Maybe you can use Exists(%Key) instead.

Re: Incremental Load- Inser & Update: Error while using multiple fields as key in concatenation

Qlik_Enthu — Tue, 15 Jun 2021 08:51:40 GMT

Hi this is just illustrative code..so i wrote it that way..but in ORiginal code it is taken care. should i have same key combination while doing full load so that this issue wont come up again?

Re: Incremental Load- Inser & Update: Error while using multiple fields as key in concatenation

JuanGerardo — Tue, 15 Jun 2021 09:08:17 GMT

I guess yes, but I'm not sure to fully understand your problem. Your error seems to be raised because of the field doesn't exists, but your logic seems correct.

Re: Incremental Load- Inser & Update: Error while using multiple fields as key in concatenation

Qlik_Enthu — Tue, 15 Jun 2021 14:39:33 GMT

I added the %KEY combination in the base load. hence the error is resolved. Can you please help me with the second part of my query? which is given below:

Also, please clarify that Not Exist will update the particular record with new field value (like last updated Date-Time) or insert entire new record with updated values along with old record?

Re: Incremental Load- Inser & Update: Error while using multiple fields as key in concatenation

JuanGerardo — Tue, 15 Jun 2021 16:22:28 GMT

I understand you will add to QV_Table the records modified since last script execution. Then you will add only the records in your QVD file (old records) that have not been inserted from the SQL statement. If newer data for %Key combinations then it will be inserted from the DB_TABLE, if not from File.QVD.

Remember in Qlik Sense you don't update records, just insert.