Incremental Load: Insert, update and delete

Alex2310 — Thu, 31 Mar 2022 22:43:57 GMT

Hi everyone,

I am currently having problems with the development of the script to carry out an incremental load from a BBDD to a QVD.
I am using the information that Qlik makes available on the topic: Isert, update and delete (https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/LoadData/use-QVD-files-incremental-load.htm).

The solution proposed in the help page works for tables that have only one primary key.

I tried to implement this solution on tables with 2 primary keys or more but I don't get the expected result.
Has anyone implemented a script for incremental loading of tables with two or more primary keys?

My script looks something like this:

Let ThisExecTime = Now( );

QV_Table:

SQL SELECT PrimaryKey1, PrimaryKey2, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(ThisExecTime)#;

Concatenate

LOAD PrimaryKey1, PrimaryKey2 X, Y FROM File.QVD

WHERE NOT EXISTS(PrimaryKey1) AND NOT EXIST(PrimaryKey2);

Inner Join SQL SELECT PrimaryKey1, PrimaryKey2 FROM DB_TABLE;

If ScriptErrorCount = 0 then

STORE QV_Table INTO File.QVD;

Let LastExecTime = ThisExecTime;

End If

Re: Incremental Load: Insert, update and delete

rubenmarin — Sun, 10 Apr 2022 07:34:19 GMT

Hi, you'll need a unique key, a unique key can be created with a combination of hte different primary keys as:

QV_Table: LOAD *, PrimaryKey1&'_'&PrimaryKey2 as UniqueKey; SQL SELECT PrimaryKey1, PrimaryKey2, X, Y FROM DB_TABLE

And check exists as

LOAD PrimaryKey1, PrimaryKey2 X, Y FROM File.QVD WHERE NOT EXISTS('UniqueKey',PrimaryKey1&'_'&PrimaryKey2);

After this you can do a "DROP Field UniqueKey;"

topic Re: Incremental Load: Insert, update and delete in App Development

Incremental Load: Insert, update and delete

Re: Incremental Load: Insert, update and delete