Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Rsaiq
Creator
Creator

Incremental Load by using Primary key (Insert & Update Method)

Hi All,

 

I need to perform increment load by using primary key.

 

I have 2 tables as below

Table1:

Id    Name

1     jhon

2      Sam

 

Table2:

1     Carry

3     Hales

 

Now required output should be like below

 

1     Carry-----Updated record

2      Sam

3      Hales------newly inserted record

I followed below script  but its not giving expected result:

ExistingData:
LOAD
ID,
Name

FROM [lib://AttachedFiles/ExistingData.qvd]
(qvd);

MaxIDTable:
Load Max(ID) as MaxID Resident ExistingData;

Let MaxID=peek('MaxID', 0, 'MaxIDTable');

 Drop table ExistingData;

Incremental_Load:
LOAD
ID, Name
FROM [lib://AttachedFiles/Data.xlsx] (ooxml, embedded labels, table is QVD2) where ID> $(MaxID);

Concatenate
LOAD
ID,
Name
FROM [lib://AttachedFiles/ExistingData.qvd] (qvd)where not Exists(ID);


STORE Incremental Load INTO [lib://AttachedFiles/ExistingData.qvd];


Drop Table Incremental Load;

Drop Table MaxIDTable;

Data:
LOAD
ID, Name
FROM  [lib://AttachedFiles/ExistingData.qvd] 

Could anyone please help me to get this resolved using insert & updated method.

 

Thanks in advance

Labels (3)
10 Replies
QFabian
MVP
MVP

Hi @Rsaiq , hope it still usefull for you :
 
Table1:
Load * INLINE [
Id_1, Name_1
1, jhon
2, Sam
];
 
 
Table2:
Load * INLINE [
Id_2, Name_2
1, Carry
3, Hales
];
 
 
Map_Table2:
mapping
Load 
Id_2 as Id_2_Map, 
Name_2 as Name_2_Map
Resident Table2;
 
 
Table:
Load
Id_1 as Id, 
    applymap('Map_Table2', Id_1, Name_1) as Name, 
if(
    applymap('Map_Table2', Id_1, Name_1) <> Name_1, 'Updated record', '') as Type    
Resident Table1;
 
concatenate
Load
Id_2 as Id, 
Name_2 as Name,
    'newly inserted record' as Type
Resident Table2
Where
not exists(Id, Id_2);
    
drop table Table1;
drop table Table2;
 
QFabian_0-1695321145718.png

 

Greetings!! Fabián Quezada (QFabian)
did it work for you? give like and mark the solution as accepted.