You are not logged in.

Unanswered posts



Important! This site has been replaced. All content here is read-only. Please visit our brand-new community at https://community.talend.com/. We look forward to hearing from you there!



#1 2010-01-23 16:33:16

mroshaw
Member
10 posts

mroshaw said:

[resolved] Mapping a single row to multiple rows

Tags: [mapping, multiple]

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

Last edited by mroshaw (2010-01-24 12:32:06)

Offline

#2 2010-01-24 05:27:22

alevy
Member
1923 posts

alevy said:

Re: [resolved] Mapping a single row to multiple rows

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.

Offline

#3 2010-01-24 10:34:22

mroshaw
Member
10 posts

mroshaw said:

Re: [resolved] Mapping a single row to multiple rows

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! smile

Offline

#4 2010-01-24 12:33:17

mroshaw
Member
10 posts

mroshaw said:

Re: [resolved] Mapping a single row to multiple rows

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!

Last edited by mroshaw (2010-01-24 12:37:18)

Offline

#5 2010-01-24 12:41:50

alevy
Member
1923 posts

alevy said:

Re: [resolved] Mapping a single row to multiple rows

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.

Offline

#6 2010-01-24 12:57:43

mroshaw
Member
10 posts

mroshaw said:

Re: [resolved] Mapping a single row to multiple rows

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! smile

Offline

#7 2010-01-24 23:02:31

alevy
Member
1923 posts

alevy said:

Re: [resolved] Mapping a single row to multiple rows

Glad to be of help.

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

Offline

#8 2011-03-17 02:09:48

alevy
Member
1923 posts

alevy said:

Re: [resolved] Mapping a single row to multiple rows

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.

Offline

#9 2011-06-12 03:30:19

alevy
Member
1923 posts

alevy said:

Re: [resolved] Mapping a single row to multiple rows

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

Offline

#10 2011-09-26 12:07:40

sfrade
Member
41 posts

sfrade said:

Re: [resolved] Mapping a single row to multiple rows

Hi,

Where I can find the tSplitRow component?

Cheers,
Seb

Offline

#11 2017-04-06 18:30:27

DV09
Guest

DV09 said:

Re: [resolved] Mapping a single row to multiple rows

Hi team,
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
a                      10
b                      10
c                      10
a                      20
b                      20
c                      20
a                      30
d                      30
e                      30
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.

Board footer

Talend Contributor Agreement - Talend Website Privacy Policy