Qlik Community

Qlik Sense Documents & Videos

Documents & videos about Qlik Sense.

Announcements
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

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

Inc_Key:
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 >>>>>>>>>>>>>>>>>>';
Inc_Key:
LOAD
MaxNum
FROM [$(vpath)QVD_Data/Inc_Key.qvd]
(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;


IncrementalData:
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;
Else

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

///////////////// Update New Key///////////////////////////////////////////////////////////
New_Inc_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';

 

Note:

  • 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

 

 

 

  

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