Incremental data load (delta load) is a very common task when extracting data from SAP, especially when extracting a big amount of data (e.g. transaction data like accounting line items).

SAP delivers business content datasources (i.e. extractors) that can be extracted using the DeltaQ component in Xtract QV. Many of those extractors provide a delta mechanism (Update mode) that is managed by SAP. Executing an extractor in the Delta update mode delivers new, changed or deleted records since the last extraction.

This sample uses the SAP extractor 0CUSTOMER_ATTR (for Customer Attributes) to develop a template to process new and changed SAP data using Xtract QV and QlikView (QVD files).

Step 1: Create an extraction in Xtract QV

In Xtract QV create an extraction for the SAP extractor 0CUSTOMER_ATTR.

sap-extractor-cust-attr

Step 2: Set the following variables in the QlikView script

Those variables allow you to use this template later with other datasources and other Xtract QV servers.

// Xtract QV Lister URL. Check the Run Dialog in Xtract QV Designer
let URL = ‘http://localhost:8085′;
// extraction name in Xtract QV
let ExtractionName = ‘extractor_customer_attr';
// Key field(s)
set KeyFields = [KUNNR]; // One key field
// use the following for an extraction with many key fields
// set KeyFields = [ADRNR] & ‘|’ & [KUNNR]; // many key fields

// folder to save qvd files
let QVDFolder = ‘qvd\';
// qvd to save data of the current status
let Extractor_CurrentStatus = ‘Extractor_CurrentStatus';
// qvd to save the data of the last delta request
let Extractor_LastDelta = ‘Extractor_LastDelta';
// qvd to save the data of all requests
let Extractor_AllRequests = ‘Extractor_AllRequests';

Step 3: Initial load

Run the extraction using the update mode C (Initialisation) and save the data to the qvd file Extractor_CurrentStatus.

// Initial load with all data (update C: Initialisation)
Extractor_CurrentStatus:
LOAD *,
$(KeyFields) as MyKey
FROM [$(URL)/?name=$(ExtractionName)&updateType=C] (txt, codepage is 65001, embedded labels, delimiter is ‘,’,msq);

Store the qvd file.
STORE Extractor_CurrentStatus into ‘$(QVDFolder)$(Extractor_CurrentStatus).qvd';

Filtering the customer number between 1000 and 1005 shows the following data in QlikView for the current status.

qv-current-initial

Optional step: Save the data to the qvd file Extractor_AllRequests to archive all the extracted SAP data.    

STORE Extractor_CurrentStatus into ‘$(QVDFolder)$(Extractor_AllRequests).qvd';

Let’s change the SAP customer data. Street number will be set to 55.

sap-change-customer

Step 4: Delta load(incremental)

Run the extraction using the update mode D (Delta)

//  – Load delta data from SAP
Extractor_LastDelta:
LOAD *,
$(KeyFields) as MyKey
FROM [$(URL)/?name=$(ExtractionName)&updateType=D] (txt, codepage is 65001, embedded labels, delimiter is ‘,’,msq);

qv-delta-request

Concatenate delta data from Extractor_LastDelta with data from Extractor_CurrentStatus, unless the keys exist in the delta data.
This will ensure that existing rows will be overwritten.

NoConcatenate
Extractor_CurrentStatus:
load Distinct * Resident Extractor_LastDelta;
Concatenate
LOAD *
FROM  ‘$(QVDFolder)$(Extractor_CurrentStatus).qvd’ (qvd)
Where not Exists(MyKey);

qv-current-delta
Optional step: Concatenate the delta data to the archive file.

// Optional step: Save all requests for the archive
NoConcatenate
Extractor_AllRequests:
load Distinct * Resident Extractor_LastDelta;
Concatenate
LOAD *
FROM ‘$(QVDFolder)$(Extractor_AllRequests).qvd’ (qvd);

// store
STORE Extractor_AllRequests into ‘$(QVDFolder)$(Extractor_AllRequests).qvd';
STORE Extractor_LastDelta into ‘$(QVDFolder)$(Extractor_LastDelta).qvd';

qv-all-request-delta
An initial load will be executed, if the file Extractor_CurrentStatus does not exist, otherwise a delta load.

// Get the creation date of the qvd file
let qvd_creation = date(QvdCreateTime(‘$(QVDFolder)$(Extractor_CurrentStatus).qvd’),’DD.MM.YYYY’);

// – If the creation date of the qvd file is not null, then load delta data (update mode)
if not isnull(qvd_creation) then

else
// Initial load with all data (update C: Initialisation)

end if

Before executing a (new) delta load, make sure that the last load was successful.

To repeat the last delta load, use the update mode type R (for Repair): &updateType=R

The extraction result has the column RequestID, which has a unique value (timestamp) for each request.

To process deleted data, you must exclude all the deleted rows using the primary key.

Download the file delta_load_customer_attr.

Written by Khoder Elzein

Khoder is responsible for presales and always has an attentive ear for our customers, prospectives and partners. When travelling in Germany or abroad, he provides customer support at PoCs, workshops and on training courses. He also looks after the further development of our software solutions. Khoder has been working in IT since the turn of the millennium; he has been a member of team Theobald since 2009. When it comes to private interests, family, nature and reading feature at the top of his list – along with innovative fusion cuisine, as you may infer from his favourite dish of Swabian cheese noodles with tabouleh.