topic Re: [resolved] Merge columns into one in Talend Studio

[resolved] Merge columns into one

Anonymous — Fri, 20 May 2016 12:13:26 GMT

Hello,
I have list of contacts and columns to store multiple email address values (e.g. columns: Id, Name, Email1, Email2, Email3). There are many name duplicates and I need to identify these duplicates based on email address not within single EmailX column, but across all Email columns.
So I guess first I need to get all Emails into one column, can anyone please advise how to design the job for this? I need to get all Emails into one column, grouped by Id and Name ideally but that I can handle later I think. Many thanks

Re: [resolved] Merge columns into one

Anonymous — Fri, 20 May 2016 12:34:56 GMT

You can use tDenormalize component to merge multiple columns into one as in below example:

NAME    COURSE1   COURSE2   COURSE3
-----------------------------------------------
DURGA MATHS       SCIENCE    ENGLISH
PRIYA    ZOOLOGY   PHYSICS    CHEMISTRY

NAME      COURSE
--------------------
DURGA    MATHS,SCIENCE,ENGLISH
PRIYA      ZOOLOGY,PHYSICS,CHEMISTRY

Re: [resolved] Merge columns into one

Anonymous — Fri, 20 May 2016 12:49:34 GMT

Thanks.. hmm ok. But it just concatenates the strings, correct? I cannot use it later to identify duplicates.. the strings dont need to have same order or for one contact there could be 1 email value and for the same (duplicated) contact in another row there could be 2 email values.
What I had in mind was for example... if I have 20 rows (1 Id column, 1 Name column and 3 email columns) the output would have 60 rows (1 Id column, 1 Name column, 1 Email column). Does it make sense? Thank you

Re: [resolved] Merge columns into one

Anonymous — Fri, 20 May 2016 13:06:22 GMT

Hi,
Is your requirement as below?
Input:
NAME    COURSE1   COURSE2   COURSE3
-----------------------------------------------
DURGA MATHS       SCIENCE    ENGLISH
PRIYA    ZOOLOGY   PHYSICS    CHEMISTRY
Output:
NAME    COURSE
-----------------------------------------------
DURGA MATHS
DURGA SCIENCE
DURGA ENGLISH
PRIYA    ZOOLOGY
PRIYA    PHYSICS
PRIYA    CHEMISTRY

To achieve the above, you can use tsplitrow component.
If your need differs or if you need more details - please supply sample input and output. I will try to assist you.

Re: [resolved] Merge columns into one

Anonymous — Fri, 20 May 2016 15:42:29 GMT

Yes Durga, it works thanks! More or less... couple of records get red during job execution saying something like "For input string: "xxxx"" and those are not generated Cannot find the error.. it is just couple of records though, most of them are properly returned.

Re: [resolved] Merge columns into one

GNarsepalli — Tue, 14 Sep 2021 09:26:43 GMT

Hi Durga, This is Gowthami, the exact same logic/requirement Ihave, could you please share the job design how to do please. It will be more helpful for me like steps/screen shots either is fine. Please assist me on this