topic Re: How to merge multiple rows? in Talend Studio

How to merge multiple rows?

Anonymous — Tue, 05 Jun 2012 07:57:30 GMT

I have a question about merge multiple rows from DB query. For example, query results:
id col1 col2 col3 col4
row1 key1 key2 AB 5
row2 key1 key2 ABC 8
Merge condition is:
row1.col1= row2.col1
and row1.col2=row2.col2
and row1.col3 like 'AB%'
and row2.col3 like 'AB%'
Then let row2.col4 = row2.col4+row1.col4. So after merge, output should be:
id col1 col2 col3 col4
row2 key1 key2 ABC 13
How can i make it? Appreciate your help!

Re: How to merge multiple rows?

Anonymous — Tue, 05 Jun 2012 08:20:25 GMT

Hi
I need to make sure one thing. All these rows are from one table or two table?
If they are in the same table, how many rows in it?
Regards,
Pedro

Re: How to merge multiple rows?

Anonymous — Tue, 05 Jun 2012 08:23:31 GMT

Hi
I need to make sure one thing. All these rows are from one table or two table?
If they are in the same table, how many rows in it?
Regards,
Pedro

Hi, Pedro
They are from one store procedure result.

Re: How to merge multiple rows?

Anonymous — Tue, 05 Jun 2012 08:29:56 GMT

Hi
What about three columns?
row1 key1 key2 AB 5
row2 key1 key2 ABC 8
row3 key1 key3 ABC 3
What's the rule for row3?
Regards,
Pedro

Re: How to merge multiple rows?

Anonymous — Tue, 05 Jun 2012 08:41:17 GMT

Hi
What about three columns?
row1 key1 key2 AB 5
row2 key1 key2 ABC 8
row3 key1 key3 ABC 3
What's the rule for row3?
Regards,
Pedro

Just output row3 directly, because it couldn't be merged on either row1 or row2. Merge conditon is
row1.col1= row2.col1
and row1.col2=row2.col2
and row1.col3 like 'AB%'
and row2.col3 like 'AB%'
if row3.col2 equals key2 not key3, it will be merged! Thanks for your quick reponse.

Re: How to merge multiple rows?

Anonymous — Tue, 05 Jun 2012 10:31:39 GMT

Hi
You might create a job as the following images.
Code in tJava

//Code generated according to input schema and output schema
Pattern pt = Pattern.compile("^AB*.");
String sm1 = col3_tMemorizeRows_1==null?"":col3_tMemorizeRows_1;
String sm0 = col3_tMemorizeRows_1; 
String col1_1= col1_tMemorizeRows_1==null?"":col1_tMemorizeRows_1;
String col2_1= col2_tMemorizeRows_1==null?"":col2_tMemorizeRows_1;
Matcher m1=pt.matcher(sm1);
Matcher m0=pt.matcher(sm0);
if((col1_1.equals(col1_tMemorizeRows_1))&&
(col2_1.equals(col2_tMemorizeRows_1))&&m1.find()&&m0.find()){
   output_row.id = id_tMemorizeRows_1;
   output_row.col1 = col1_tMemorizeRows_1;
   output_row.col2 = col2_tMemorizeRows_1;
   output_row.col3 = col3_tMemorizeRows_1;
   output_row.col4 = col4_tMemorizeRows_1+col4_tMemorizeRows_1;
}else{
output_row.id = input_row.id;
output_row.col1 = input_row.col1;
output_row.col2 = input_row.col2;
output_row.col3 = input_row.col3;
output_row.col4 = input_row.col4;
}

Regards,
Pedro

Re: How to merge multiple rows?

Anonymous — Thu, 07 Jun 2012 03:58:09 GMT

Hi
You might create a job as the following images.
Code in tJava

//Code generated according to input schema and output schema
Pattern pt = Pattern.compile("^AB*.");
String sm1 = col3_tMemorizeRows_1==null?"":col3_tMemorizeRows_1;
String sm0 = col3_tMemorizeRows_1; 
Matcher m1=pt.matcher(sm1);
Matcher m0=pt.matcher(sm0);
if((col1_tMemorizeRows_1==col1_tMemorizeRows_1)&&
(col2_tMemorizeRows_1==col2_tMemorizeRows_1)&&m1.find()&&m0.find()){
   output_row.id = id_tMemorizeRows_1;
   output_row.col1 = col1_tMemorizeRows_1;
   output_row.col2 = col2_tMemorizeRows_1;
   output_row.col3 = col3_tMemorizeRows_1;
   output_row.col4 = col4_tMemorizeRows_1+col4_tMemorizeRows_1;
}else{
output_row.id = input_row.id;
output_row.col1 = input_row.col1;
output_row.col2 = input_row.col2;
output_row.col3 = input_row.col3;
output_row.col4 = input_row.col4;
}

Regards,
Pedro

Hi Pedro,
i am still confused how did you generate the code? where is your merge conditon definition? Thanks a lot.

Re: How to merge multiple rows?

Anonymous — Thu, 07 Jun 2012 04:01:31 GMT

Hi
The merge condition definition is decided by 'if' statement.
if((col1_tMemorizeRows_1==col1_tMemorizeRows_1)&&
(col2_tMemorizeRows_1==col2_tMemorizeRows_1)&&m1.find()&&m0.find())
In fact, your requirement is complicated. It took me time to write these code.
Regards,
Pedro

Re: How to merge multiple rows?

Anonymous — Thu, 07 Jun 2012 04:29:19 GMT

Hi
The merge condition definition is decided by 'if' statement.
if((col1_tMemorizeRows_1==col1_tMemorizeRows_1)&&
(col2_tMemorizeRows_1==col2_tMemorizeRows_1)&&m1.find()&&m0.find())
In fact, your requirement is complicated. It took me time to write these code.
Regards,
Pedro

Thanks again, Pedro. But looks like records are not merged.
input:
row1;key1;key2;AB;5
row2;key1;key2;ABC;8
row3;key1;key3;ABC;3
output should be:
row2;key1;key2;ABC;13
row3;key1;key3;ABC;3

Re: How to merge multiple rows?

Anonymous — Thu, 07 Jun 2012 04:39:31 GMT

Hi
I have corrected my mistake.
You will see the right solution at Comment #6 this time.
Besides, you have to remove the first line manually.
Regards,
Pedro

Re: How to merge multiple rows?

Anonymous — Tue, 05 Aug 2014 20:43:59 GMT

Hi,
I need solution on the below query.
i have two tables table A, table B
Table A has one column task_id
Table B has two cols task_id,task_status
task_id status
----------- -----------------
10 in progress
10 completed
I need table c to have
task_id history_status
---------- ---------------
10 in progress,completed
thanks
Devashsi

Re: How to merge multiple rows?

Anonymous — Wed, 06 Aug 2014 09:27:05 GMT

Hi,
This could be achieved using denormalization... pl check following scenario, you will get an idea.
https://help.talend.com/search/all?query=tDenormalize&content-lang=en
Vaibhav