topic Re: How we can Perform Update(only) in incremental load ? in QlikView

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

madhubabum — Thu, 26 Nov 2015 12:24:41 GMT

Hi Experts

How we can Implement Update only in Incremental load ?

Note : Ignore Insert records (Perform only Update )

Thanks

Madhu

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

Anonymous — Thu, 26 Nov 2015 12:32:05 GMT

see this basic part:

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

Incremental Load in QlikView – Part2 | Learn QlikView

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

madhubabum — Thu, 26 Nov 2015 12:43:25 GMT

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

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

Anonymous — Thu, 26 Nov 2015 12:55:33 GMT

may be this:

how to do incremental load using primary key

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

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

effinty2112 — Thu, 26 Nov 2015 12:56:46 GMT

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;

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

madhubabum — Thu, 26 Nov 2015 13:07:49 GMT

Thanks for Your Reply

Itz working Perfectly

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

effinty2112 — Thu, 26 Nov 2015 13:21:34 GMT

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;

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

madhubabum — Thu, 26 Nov 2015 14:02:30 GMT

Thanks Andrew Walker ,

Script working fine

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

richard_chilvers — Thu, 26 Nov 2015 14:07:30 GMT

Please mark the correct answer.

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

effinty2112 — Thu, 26 Nov 2015 14:41:45 GMT

Thank you Richard.

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

effinty2112 — Fri, 27 Nov 2015 08:26:16 GMT

Hi madhu,

I thought the script I gave you was too long and could have been made shorter. It can and 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,

Key as ModKey;

Load * Inline [

Key, Hours, Status

100, 2, Completed

101, 3, Completed

102, 10, Completed

103, 5, Completed];

Drop Field HashKey;

NoConcatenate

NewDataSet:

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

DROP Table OldDataSet;

Concatenate (NewDataSet)

LOAD Key, Hours, Status Resident ModifiedData;

DROP Table ModifiedData;