Four Stars

Insert Update and delete in destination file

Hi Team,

 

I have a file input and file output,

It's an every day load and i will get file everyday
I want to lookup my output file and check if there is any change in the new input file and if the record doesn't exists  in the output file , we need to insert into the output  file, if the row already exists , I need to update the data in the output file .

 

My source and target are files, So I'm stuck in the implementation part.

Can anyone help me out on this.

 

Thanks for your help.

 

Mahesh

1 ACCEPTED SOLUTION

Accepted Solutions
Forteen Stars TRF
Forteen Stars

Re: Insert Update and delete in destination file

You can also join each main row field to the corresponding one in the lookup.

You need 2 steps:

1- does the record exist in the previous file?

2- no, this is a new record. yes, have a 2nd tMap with all fields joined to each other to get changed records.


TRF
3 REPLIES
Forteen Stars TRF
Forteen Stars

Re: Insert Update and delete in destination file

If you have fields such as unique key and version number, you may just use tMap with inner join with new file as main row and previous one as lookup.
If you have to be able to detect any change value for any field you have to get the MD5 value for all the fields but the key giving the following schema: keyField, md5Field. Then you can compare both versions using a tMap with new MD5 file as main row and previous one as lookup. Use keyField for the inner join.
If key exists but md5 is not the same value, the record has been updated.
If key doesn't exist, the record is new.
If you need to detect deleted records, you need an other subjob where you main row becomes the lookup, then rejected records are deleted ones.

This link may help you https://community.talend.com/t5/Design-and-Development/How-to-generate-MD5-hash-value/td-p/43927

TRF
Four Stars

Re: Insert Update and delete in destination file

Thanks for your quick reply,

 

But is there any other way to do other than writing a routine ?

 

Thanks,

Mahesh

Forteen Stars TRF
Forteen Stars

Re: Insert Update and delete in destination file

You can also join each main row field to the corresponding one in the lookup.

You need 2 steps:

1- does the record exist in the previous file?

2- no, this is a new record. yes, have a 2nd tMap with all fields joined to each other to get changed records.


TRF