[resolved] Mapping a single row to multiple rows

[Anonymous]

Hi!
First off, I wanted to say what an amazing application Talend Open Studio is! It's unbelievable that it's an open source project. Well done to everyone involved!
I had a wee question that I hope someone could help me with:
My input data source, representing a contact, has two phone numbers in a single row: Home, Mobile
My output data source stores phone numbers is a separate table, with one row for each type, linking each back to the contact. Therefore, one row in source goes to two rows in target.
How do I configure the tMap component to map the two columns from the single source row, into two rows in the target schema? There's a type field in the target schema that I'd also like to populate with 'Home' or 'Mobile', depending on which source column goes into that row.
For context, if it helps, I've just started looking at this application to assist in data migration using the Siebel CRM software from Oracle. It looks like this is the perfect tool for populating EIM tables from source data of all types, enabling us to get it into the Siebel database. We've been using SQL Loader and Access (!) so far and it's proving to be a nightmare to run and maintain.
I hope this makes sense and isn't a complete nube question. You help is very much appreciated!
Kind regards,
mroshaw

[alevy · Accepted solution]

Your post is unclear if the home and mobile numbers are in one field or two.
Assuming they are in two fields, use a lookup table for your tMap containing one column and two rows with the values "Home" and "Mobile" but don't link the main input table to the lookup table. This will give you two output rows for each input row and you can map the lookup table value to your type field and the relevant phone number depending on the lookup value e.g. PhoneTypes.Type.equals("Home")?InputData.HomeNumber:InputData.MobileNumber. See attached screen print.
If your two phone numbers are in one field in the source data, drop a tExtractDelimitedFields or tExtractPositionalFields component in between your source table and the tMap.

[Anonymous]

alevy, thank you for the response!
The data is coming in as two separate fields / columns, so I'll give you first suggestion a try. Thank you very much for your help!

[Anonymous]

Hello again!
My problem now is that I'm getting duplicate rows appearing in my output schema - obviously from including the lookup table in the tMap.
I've attached the job and the tMap configuration, which will hopefully make it a lot clearer as to what I'm trying to do.
Can you point me in the direction I need to go to get a single record in the CONTACT and ADDRESS tables, with multiple records in COMMS for the two phone numbers?
Sorry to be a pain - I'm struggling to get my head around this!

[alevy]

You could split the job into two tMaps, the first creating the contacts and addresses tables and sending the remaining data on to a second tMap with the lookup to create the comms table.

[Anonymous]

Wow - it worked. Brilliant! I've attached a screenshot of the job for reference, in case anyone else has a similar requirement.
Thank you soooo much for your help with this!
I love this product - it's amazing! It's going to make my life so much easier and it's just so simple and intuitive to use.
Loving it!

[alevy]

Glad to be of help.
Please set this topic as resolved (option at bottom right of your original post).

[alevy]

A possibly better approach to the Cartesian-join approach I suggested above is to use tMap's ability to unite two output tables into one output flow. When creating a new output within tMap, just select the "Create join table from" option rather than the default "New output" option. There will then be one flow out of the tMap that will include the rows from both of those output tables within tMap. Those output tables can have different filter and field expressions. This approach should allow the above job to only have one tMap.

[alevy]

From v4.2.0, the tSplitRow component handles this very well.

[Anonymous]

Hi,
Where I can find the tSplitRow component?
Cheers,
Seb