Qlik Community

Qlik Sense Documents & Videos

Documents & videos about Qlik Sense.

See why Qlik is recognized as a Leader for the 10th year in a row – and discover how Qlik can help put your business in the lead. Get Report

Incremental without Date Field or any Identifier in Source Table

Valued Contributor II

Incremental without Date Field or any Identifier in Source Table

Hi All,

I have come across few threads seeking options to perform Incremental Load without Last Updated Date field ,made available in source data, in such case one option that would help us achieve the insert new records via incremental  load functionality is by creating a Sequence Generator in Qlik Script , please check the below for the complete script also attached is a sample application enabling the same:

Variables Declared in the Script:

let vpath= 'lib://Folder_Conn/';
let vqvdpath ='lib://Folder_Conn/QVD_Data/';
let vfilename= 'Data.qvd';
let vfilesize= FileSize('$(vqvdpath)$(vfilename)');

/////////////////// Script for the Initial Load(Full Load) based on the presence of QVD File that hosts the data loaded

if len('$(vfilesize)')=0 then
trace '<<<<<<<<<<< Full Load>>>>>>>>>>>>>>>>>>>>>';

[First Load]:
Load RecNo() as SI,
FROM [$(vpath)Incremental_Data.xlsx]
(ooxml, embedded labels, table is [Sheet1]);

Store [First Load] into [$(vpath)QVD_Data/Data.qvd](qvd);

Load num(max(FieldValue(('SI'),Recno()))) as MaxNum
AutoGenerate num((FieldValueCount('SI')));

Drop Table [First Load];

Store Inc_Key into [$(vpath)QVD_Data/Inc_Key.qvd](qvd);
Let v_IncKey=Peek('MaxNum',0,'Inc_Key');
Drop Table Inc_Key;

trace '<<<<<<<<<<<<<<<< Initial Load No of rows is' $(v_IncKey);

///////////////////////////////////////////////////Incremental Load with Insert only Option//////////////////////////////////

ElseIf len('$(vfilesize)') > 0 then

Trace '<<<<<<<<<<<<<<<<<<<Incremental Load >>>>>>>>>>>>>>>>>>';
FROM [$(vpath)QVD_Data/Inc_Key.qvd]

Let v_IncKey=Peek('MaxNum',0,'Inc_Key');
//Let v_MaxNum=Peek('MaxNum',0,'Inc_Key');
trace '<<<<<<<<<<<<<<<>>>>>>>>>>>';
trace '<<<<<<<<REC NUM'$(v_IncKey);
Drop Table Inc_Key;

Load * ,
Recno() as SI
FROM [$(vpath)Incremental_Data.xlsx]
(ooxml, embedded labels, table is [Sheet1])
where RecNo()>$(v_IncKey);

let vRows=NoOfRows('IncrementalData');

if $(vRows)= 0 then
trace '<<<<<<<<<<<<<<<No New Rows';
Exit Script;

trace '<<<<<<<<<<<No of Delta Records are' $(vRows);
///////////////////////////// Concatenate Incremental Data with Existing Data
load *
FROM [$(vpath)QVD_Data/Data.qvd](qvd);
Store IncrementalData into [$(vpath)QVD_Data/Data.qvd];

///////////////// Update New Key///////////////////////////////////////////////////////////
Load num(max(FieldValue(('SI'),Recno()))) as MaxNum,
'New' as flag
AutoGenerate num((FieldValueCount('SI')));

let vNewKey= Peek('MaxNum',0,'New_Inc_Key');
Trace '<<<<<<<<<<<<<<< Key post Incremental Load is'$(vNewKey);
Store New_Inc_Key into [$(vpath)QVD_Data/Inc_Key.qvd];
drop table New_Inc_Key;
drop table IncrementalData;

trace '<<<<<<<<<<<<<<<<<<Done';



  • I have used Qlik Sense and hence the Lib statements in the variable path definitions, please change it accordingly for Qlikview implementation.
  • The same code can be extended to perform Update and Delete Operations via Incremental Load using the preferred methods with the usage of a primary key





Version history
Revision #:
1 of 1
Last update:
2 weeks ago
Updated by: