Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
Clever_Anjos
Employee
Employee

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

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

 

2 Comments
blaise
Partner - Specialist
Partner - Specialist

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.
683 Views
qlikster
Contributor II
Contributor II

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.

186 Views