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);

[blaise]

I highly recomend to use fieldvalue() + autogenerate(fieldvaluecount()) instead of resident load like;

Max(FieldValue('OrderID',recNo())) as MaxOrderID AutoGenerate(FieldValueCount('OrderID'));

Should perform much faster on large datasets.

[qlikster]

Hi,

thanks for this, best example I’ve found so far on the merge logic.

@blaise  Do you have a complete example with fieldvalue ? Haven’t been able to get this to work.

[JHuis]

Dear @Clever_Anjos ,

//i am trying the Merge prefix solution from above but it is slower then traditional incremental load.

i'm sorry my analysis was not good. i'm seeing a big performance update true merge. Thanks for the explanation! 

[rva]

@qlikster 

Load min(OrderDate) as MinDate,
max(OrderDate) as MaxDate;
Load FieldValue('%OrderDate',IterNo()) as OrderDate
autogenerate(1)
while not IsNull(FieldValue('%OrderDate',Iterno()));