Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator II
Creator II

Incremental load using Key Fiel

Hi,

Today I've below data in my QVD.

ID Price1 Price2 Price3
1 100 100 100

 

In the Main source the Price's of ID '1' has been updated to below.

ID Price1 Price2 Price3
1 200 200 200

 

So my requirement is instead of updated the QVD with new Price's I would like to see the two lines with the new and old price like below in the QVD.

ID Price1 Price2 Price3
1 100 100 100
1 200 200 200

 

I tried below code but it is not working.

QVD:
Load * Inline
[
ID,Price1,Price2,Price3
1,100,100,100
];

MaxQVD:
LOAD max(ID) as MaxID
Resident QVD;

Let vMaxID = peek('MaxID',-1);

DROP Table QVD;

MainNewData:
Load * Inline
[
ID,Price1,Price2,Price3
1,200,200,200
];

Concatenate
Load * Inline
[
ID,Price1,Price2,Price3
1,100,100,100
] where ID>$(vMaxID);

STORE MainNewData Incremental.QVD(qvd);

Drop Table MainNewData ;

Exit Script;

1 Reply
Or
MVP
MVP

Given that there doesn't seem to be any indicator that the row has updated, you can't really do an incremental load here, near as I can tell. You would need to load all of the data from both sources to find out if any IDs have had their values updated, at which point you can just Load Distinct on the concatenation of both sources (which would eliminate any duplicate ID rows which means nothing has been updated).