How we can Perform Update(only) in incremental load ?

[madhubabum]

Hi Experts

How we can Implement Update only in Incremental load ?

Note : Ignore Insert records (Perform only Update )

Thanks

Madhu

[Anonymous]

see this basic part:

http://www.learnallbi.com/incremental-load-in-qlikview-part1/

Incremental Load in QlikView – Part2 | Learn QlikView

[madhubabum]

Thanks for reply

In our case we are not using Date field

Example :

OldDataSet:

Key,Hours,Status

100,2,Completed

101,3,Completed

102,4,Pending

NewDataSet:

100,2,Completed

101,3,Completed

102,10,Completed

103,5,Completed

Here I want to update(only) , which record is modified (102 record only)

I dont want to load new record .

Please help to write the script

Thanks

Madhu

[Anonymous]

may be this:

how to do incremental load using primary key

http://www.quickintelligence.co.uk/qlikview-incremental-load/

[effinty2112]

Hi madhu,

This script will give you a table called ModifiedData

Key	Hours	Status
102	10	Completed

OldDataSet:

Load

*,

Hash128(Key & '|' & Hours & '|' &  Status) as HashKey;

Load * Inline [

Key, Hours, Status

100, 2, Completed

101, 3, Completed

102, 4, Pending];

NoConcatenate

ModifiedData:

Load

*

Where Exists(Key) and Not Exists(HashKey);

Load

*,

Hash128(Key & '|' & Hours & '|' &  Status) as HashKey;

Load * Inline [

Key, Hours, Status

100, 2, Completed

101, 3, Completed

102, 10, Completed

103, 5, Completed];

Drop Table OldDataSet;

Drop Field HashKey;

[madhubabum]

Thanks for Your Reply

Itz working Perfectly

[effinty2112 · Accepted solution]

Hi madhu,

This script gives you the table of your original data but with modification to changed records and without addition of new:

Key	Hours	Status
100	2	Completed
101	3	Completed
102	10	Completed

There's a lot of renaming and dropping going on here and it can possibly be simplified. Anyway here it is:

OldDataSet:

Load

*,

Hash128(Key & '|' & Hours & '|' &  Status) as HashKey;

Load * Inline [

Key, Hours, Status

100, 2, Completed

101, 3, Completed

102, 4, Pending];

NoConcatenate

ModifiedData:

Load

*

Where Exists(Key) and Not Exists(HashKey);

Load

*,

Hash128(Key & '|' & Hours & '|' &  Status) as HashKey;

Load * Inline [

Key, Hours, Status

100, 2, Completed

101, 3, Completed

102, 10, Completed

103, 5, Completed];

Drop Field HashKey;

ModifiedData1:

Load

Key as ModKey,

Hours,

Status

Resident ModifiedData;

drop Table ModifiedData;

RENAME Table ModifiedData1 to ModifiedData;

NoConcatenate

NewDataSet:

Load * Resident OldDataSet Where Not Exists(ModKey, Key);

DROP Table OldDataSet;

NoConcatenate

ModifiedData1:

Load

ModKey as Key,

Hours,

Status

Resident ModifiedData;

drop Table ModifiedData;

RENAME Table ModifiedData1 to ModifiedData;

Concatenate (NewDataSet)

LOAD * Resident ModifiedData;

DROP Table ModifiedData;

[madhubabum]

Thanks Andrew Walker ,

Script working fine

[richard_chilvers]

Please mark the correct answer.

[effinty2112]

Thank you Richard.