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!
Last edited by mroshaw (2010-01-24 12:32:06)
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.
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!
Last edited by mroshaw (2010-01-24 12:37:18)
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.
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.
I am looking to do some multiple row mapping and unsure how to resolve the below situation. Any inputs on how to go about will be very helpful.
mapping table has below entries
column 1 column 2
bundle 1 a,b,c
bundle 2 a,d,e
now I want to use this mapping table in my tmap vlook where I have to look up for below values
lookup value quantity
bundle 1 10
bundle 1 20
bundle 2 30
I am expecting the output as below
column 1 quantity
What I am looking for is how do I map 1 value to give me 3 values in the output. I tried by changing the join condition in the tmap, but it din't seem to work for me.
Thanks in advance.