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

[vivekjayapalan]

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

[Anonymous]

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).

[_AnonymousUser · Accepted solution]

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

[vivekjayapalan]

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

Thanks,
Vivek

[vivekjayapalan]

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.

[Anonymous]

Yes you have to separate them later into your fields.

[vivekjayapalan]

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,

[vivekjayapalan]

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