Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2019-08-15 12:35 PM

How to update the records in the existing file using lookup

Hi,

I am trying to load a file everyday which are having new records as well as updated records.

1. Master File

2. Incremental File (which comes everyday).

 

I want to update the records in master coming from incremental file .

Can anybody help. It's urgent

Labels (3)
Labels
200 Views
0 Likes
1 Reply
JR1
Creator III
Creator III
‎2019-08-17 04:49 PM

Try this (assuming your files are CSV files):

 

  • First, create a backup of your master file using tFileCopy.
  • Create a subjob with tFileInputDelimited (incremental file) --> tMap --> 2 x tFileOutputDelimited and have your master file as a lookup (tFileInputDelimited) in the tMap. Depending on how many records you have in your master file, you may need to switch on "Store temp data" and set a temp directory. You then map your input (incremental) to the lookup (master) by matching their keys and set the Join Model to "Inner Join".
    • The first output (temporary file 1) should be set to "Catch lookup inner join reject" = true (use the same schema as in your master file). It will contain the records which are unknown in the master and hence have to be inserted into the master.
    • The second output (temporary file 2) should be set to "Catch lookup inner join reject" = false (use the same schema as in your master file). It will contain the records which are already present in the master and hence have to be replaced with new versions.
  • The next subjob will remove the records from the master which have new versions in your incremental file. Create a subjob with tFileInputDelimited (master) --> tMap --> tFileOutputDelimited (temporary master) and have temporary file 2 (from above) as a lookup (tFileInputDelimited) in the tMap (consider using "Store temp data" again). You then map your input to the lookup by matching their keys and set the Join Model to "Inner Join". The output should be set to "Catch lookup inner join reject" = true (use the same schema as in your master file). It will contain a version of your master with all the rows removed for which there are new versions in your incremental file.
  • The next subjob will add the new records from your incremental file to your new master. Create a subjob with tFileInputDelimited (temporary file 1) --> tFileOutputDelimited (temporary master) and set "Append" = true in the tFileOutputDelimited component.
  • The next subjob will add the updated records from your incremental file to your new master. Create a subjob with tFileInputDelimited (temporary file 2) --> tFileOutputDelimited (temporary master) and set "Append" = true in the tFileOutputDelimited component.
  • At the end, rename the temporary master to master and you will have the updated master file with all the new and updated records from the incremental file.

Does this help? I hope I did not mess anything up here, but I do not have the possibility to actually do this in Talend right now.

200 Views
0 Likes