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

[Rsaiq]

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

[QFabian]

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;