Need to update the lookup tables while performing a tmap

[Anonymous]

I am in the process of converting a flat database table into a relational database and I'm using the Data Integration component to do so.
I've been able to perform the following actions successfully:
1) connect to my flat database table to access all the data
2) using tMap, I'm mapping each field in the flat table to a corresponding lookup table and returning a key
3) connect to the relational database and write the key values for each field
Here's my problem, when performing the lookup in step 2 from above, certain values are not in the lookup tables, causing a null value to be returned. When this situation occurs I'd like to add the missing value to the lookup table and return the new value's ID to be stored.
I've scoured these forums as well as stack overflow but couldn't seem to find anything explaining how to do this.

[Anonymous]

Hi,

Here's my problem, when performing the lookup in step 2 from above, certain values are not in the lookup tables, causing a null value to be returned. When this situation occurs I'd like to add the missing value to the lookup table and return the new value's ID to be stored.

It seems that you can use another output (lookup table)to accept certain values causing a null value. And set a new Var(id) in tMap for it.
Is it what are you looking for? Elaborating your case with an example with input and expected output values is preferred.
Best regards
Sabrina

[Anonymous]

Hi Sabina,
Thank you for your response. I have uploaded 2 additional images to help clarify what I'm doing.
Image#3 gives you an idea of what I'm trying to accomplish. You'll see question marks in one of the fields in the lower table, this signifies the issue I'm trying to resolve.
Image #2 gives you an idea of the tmap schema I'm using. I'd like to modify this schema so that whenever a value in the cars table is not present in any of the lookup tables, the system can add that missing value to the appropriate lookup table and return the associated ID.

[alevy]

Simplest approach is to first add all the missing lookup values you need to those tables before you read the tables to retrieve the IDs you need.

[Anonymous]

Thanks for your response alevy.
Gotcha, that makes sense. Could you please give me a rough idea of how to do that (or point me in the right direction)? I'm very new to Talend.

[alevy]

One way is to have a series of subjobs, each one simply reading the distinct values for one of the lookups from your source table and using the "insert if not exist" option to add them to the relevant lookup table. Once all of those subjobs are complete, move on to the subjob you already have to do the transformation.