topic Re: [resolved] Merge multiple row data into single row for group of data. in Talend Studio

[resolved] Merge multiple row data into single row for group of data.

vivekjayapalan — Mon, 08 Feb 2016 19:52:33 GMT

Hi,
I need help in doing the following
Have attached the scenario i am trying to do in Talend

Couldn't find any component where you can do a un-pivot kind of logic.
Any info on this will be great help.
Thanks,
Vivek

Re: [resolved] Merge multiple row data into single row for group of data.

Anonymous — Mon, 08 Feb 2016 20:51:16 GMT

First step is denormalizing the second flow to get the rows as columns. Use tDenormalize to do that.
Now join flow on to it (over the application_id).

Re: [resolved] Merge multiple row data into single row for group of data.

_AnonymousUser — Mon, 08 Feb 2016 21:55:25 GMT

Hi, i have developed quickly a small job to resolve this Denormalization
see solution here ( )
TalendExpert.com Tutorial

Re: [resolved] Merge multiple row data into single row for group of data.

vivekjayapalan — Tue, 09 Feb 2016 13:37:47 GMT

Hi ahallam,
could not find any attachment on your reply.

Thanks,
Vivek

Re: [resolved] Merge multiple row data into single row for group of data.

vivekjayapalan — Tue, 09 Feb 2016 21:40:48 GMT

Hi Jlolling ,
I tried your solution, and only option we have is to concatenate these fields, is there any option to put it as separate fields? I mean in the tDenormalize.

Re: [resolved] Merge multiple row data into single row for group of data.

Anonymous — Tue, 09 Feb 2016 22:58:52 GMT

Yes you have to separate them later into your fields.

Re: [resolved] Merge multiple row data into single row for group of data.

vivekjayapalan — Thu, 18 Feb 2016 18:47:00 GMT

Hi,

Can anyone specify how we can split the concatenated value into multiple columns into a table? i have multiple incoming fields which i need to split into multiple output fields. i know i can split one column into multiple using tExtractRegexFields, but i need to know how we can split multiple columns into multiple columns.

if there is no component for the doing the above scenario then is there any option for case statement to find delimiter "|" and then split the fields or substring only values before first "|" in col1 and value between first and second delimiter in col2 and value between second and third delimiter in col3 and so on... like this i have to split each column which i merged in source into multiple in target.
is there any easy way to do that.
Thanks,

Re: [resolved] Merge multiple row data into single row for group of data.

vivekjayapalan — Tue, 23 Feb 2016 14:30:56 GMT

Finally got it working.
There is no direct solution to achieve the above scenario, so i wrote custom code in tJavaRow to achieve the data shown in the below screenshot,

By writing the following code i was able to get the data as shown in the above screenshot

//***tJavaRow code begin****//
java.util.List<String> ColumnList = new java.util.ArrayList<String>();
String value1 = "";
String value2 = "";
String value3 = "";
String value4 = "";
// APP_ID: as APP_ID doesn't need to be split as this data is uniquie the same is assigned directly into output_row//
output_row.APP_ID = input_row.APP_ID;

ColumnList.add(input_row.FIRST_NAME);
ColumnList.add(input_row.LAST_NAME);
ColumnList.add(input_row.ADDR);
for(String column:ColumnList)
	{
	String[] splitArray = column.split("\\|");   
	for (int i = 0; i < splitArray.length; i++)
		{
		if(i == 0)
			{
			value1 = splitArray;	
			}  if (i == 1)
				{
				value2 = splitArray;
				}  if (i == 2)
					{
					value3 = splitArray;
					}  
		}
		if(column.equalsIgnoreCase(input_row.FIRST_NAME))
			{
			output_row.FIRST_NAME_P_APPLICANT = value1;
			output_row.FIRST_NAME_CO_APP1 = value2;
			output_row.FIRST_NAME_CO_APP2 = value3;			
			}	
		if(column.equalsIgnoreCase(input_row.LAST_NAME))
			{
			output_row.LAST_NAME_P_APP = value1;
			output_row.LAST_NAME_CO_APP1 = value2;
			output_row.LAST_NAME_CO_APP2 = value3;			
			}	
		if(column.equalsIgnoreCase(input_row.ADDR))
			{
			output_row.ADDR_P_APP = value1;
			output_row.ADDR_CO_APP1 = value2;
			output_row.ADDR_CO_APP2 = value3;			
			}
	}
//***tJavaRow code end****//

Using ArrayList and split function we can split single column into any number of columns.
Thanks