Processing incremental data load of SAP datasources (extractors) with Xtract QV and QlikView

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 QVIn Xtract QV create an extraction for the SAP extractor 0CUSTOMER_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.


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.



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



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



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



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.