validate expenditure code or replace with suspense code

[Anonymous]

I have a Talend job that reformats a simple input file into a complex data structure to load into a system interface.

In the data there is an expenditure code that I need to validate by confirming that it exists in a database table and if it dosen't then replace it with a suspense code. The database table is in a MS SQL database.  

Can anyone suggest the best way to approach this?

So I need to read a line from the .csv file confim if the expenditure code exists in the database table, if it does write the line to the output file unchanged, if not, replace the expenditure code with a 14 character string which is the suspense code. The suspense code will be the same for all invalid expenditure codes and could be a hard coded string.

Many thanks, Jerry

[fdenis]

it depends on the amount of data and the capacity of the servers, if it is a one-time or permanent task.

so do it and come back if you have troubles.

[Anonymous]

The input file data will be around 100 rows, the database table has around 10000 rows, so not massive.
I'm really looking for which Talend components to use and what order to connect them, as I've not created a job using this method before.
Thanks, Jerry

[fdenis · Accepted solution]

add tMap on an orange link (data).

add a tXXXinput with the sql query (XXX is your db type).

link this on to the tmap (you are going to manage your look-up here).

read tmap doc and sample you are going to success.

[Anonymous]

Thanks for your help fdenis.

So I've created the tmap, but I'm not sure how to filter it.

I've created two outputs, one where the expenditure code exists in the database, the expenditure code is output unchanged. 

The other where the expenditure code is not in the database and the expenditure code is replaced by " NPT00322100000 " , but it's not working.

I've been trying to upload a screen shot of the tmap, but that's not working either. I'll post this message then see if I can upload the screen shot.