[resolved] Merge columns into one

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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.

[GNarsepalli]

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