Qlik Sense Incremental Load using Merge

[Clever_Anjos]

With the new MERGE prefix, your incremental QVD script can have a much cleaner and faster code.

[Clever_Anjos]

Since the introduction of the new  MERGE prefix (https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...) has become much easier to create an incremental load using a Qlik script. This command receives a table with insert, update, and delete operations and can change a table already loaded in memory.

For the examples below we have two assumptions:

• OrderID is the table's primary key (if you have a composite key, you need to adjust it)
• update_dt stores the date/time of the last record insert/change

Let's see the examples:

1 -"Insert Only Case

QVDFile='lib://DataFiles/NW.Orders_InsertOnly.qvd';
if FileSize('$(QVDFile)') > 0 then //If there is already extracted data
[NW.Orders]:
Load 
OrderID,
CustomerID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry
from [$(QVDFile)](qvd);
t:load Max(OrderID) as MaxOrderID Resident [NW.Orders];
LastOrderID=Peek('MaxOrderID'); // Retrieves the last OrderID
Drop Table t;
else 
LastOrderID = '0';
[NW.Orders]:load null() as OrderID AutoGenerate 0; // fake table
endif

Merge(updated_dt) on OrderID Concatenate([NW.Orders])
Load If(Exists(OrderID),'U','I') as Operation, *; // New record? based on OrderID
SELECT 
OrderID,
CustomerID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry,
updated_dt
FROM northwind.orders
Where OrderID > '$(LastOrderID)';

Store [NW.Orders] into [$(QVDFile)](qvd);

2 - "Insert" & "Update" Case
QVDFile='lib://DataFiles/NW.Orders_InsertUpdate.qvd';
if FileSize('$(QVDFile)') > 0 then //If there is already extracted data
[NW.Orders]:Load 
OrderID,
CustomerID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry,
updated_dt
from [$(QVDFile)](qvd);
t:load Max(updated_dt) as updated_dt Resident [NW.Orders];
updated_dt=Timestamp(Peek('updated_dt'),'YYYY-MM-DD hh:mm:ss.fff'); // Retrieves the last udpate
Drop Table t;
else 
updated_dt = '2000-01-01';
[NW.Orders]:load null() as OrderID AutoGenerate 0; // fake table
endif

Merge(updated_dt) on OrderID Concatenate([NW.Orders])
Load If(Exists(OrderID),'U','I') as Operation, *; // New record? based on OrderID
SELECT 
OrderID,
CustomerID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry,
updated_dt
FROM northwind.orders
Where updated_dt > '$(updated_dt)';

Store [NW.Orders] into [$(QVDFile)](qvd);

3 – Insert / Update / Delete Case 

QVDFile='lib://DataFiles/NW.Orders_InsertUpdateDelete.qvd';
if FileSize('$(QVDFile)') > 0 then //If there is already extracted data
[NW.Orders]:Load 
OrderID,
CustomerID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry,
updated_dt
from [$(QVDFile)](qvd);
t:load Max(updated_dt) as updated_dt Resident [NW.Orders];
updated_dt=Timestamp(Peek('updated_dt'),'YYYY-MM-DD hh:mm:ss.fff'); // Retrieves the last udpate
Drop Table t;
else 
updated_dt = '2000-01-01';
[NW.Orders]:load null() as OrderID AutoGenerate 0; // fake table
endif

Merge(updated_dt) on OrderID Concatenate([NW.Orders])
Load If(Exists(OrderID),'U','I') as Operation, *; // New record? based on OrderID
SQL SELECT OrderID,
CustomerID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry,
updated_dt
FROM northwind.orders
Where updated_dt > '$(updated_dt)';

//Keeping only records which OrderID is still in database
Inner Keep([NW.Orders])
SQL SELECT DISTINCT OrderID
FROM northwind.orders;

Store [NW.Orders] into [$(QVDFile)](qvd);