Optionally merge row to previous row if conditions apply

Two Stars

Optionally merge row to previous row if conditions apply

Hi there Talend Community,


I've got an interesting problem. We have a system with limited export / configurations options that generates and Excelfile like this.




We get n number of rows. The main row has column A-F populated with real data (no row ID).

Then optionally we get one, or more rows below with A-F not containing any values but instead G has a value. Now I somehow want to loop over the rows and check if the row doesnt contain any info in column A-F and then merge/concat it to my original row.


Does this make any sense to you? I'm reading up on tMemorizeRows and tJavaFlex but the tricky part here is that it's not always the case with the additional rows and then can be 1 or more so we don't really now how many extra rows there are.


I'm searching in the dark so I thought I'd give this community a try.



Sixteen Stars

Re: Optionally merge row to previous row if conditions apply

A simple way to approach this is to read your data in (columns A through to G) and calculate a "key" column. The "key" column would indicate when a new record appears in column A. When a value does not appear in A, then the same "key" is used. So in the following dataset.....


345234 1
126378 2
736435 3
984367543 4
255633 5
243544 6

....you would get a "key" like above. Then all you need to do is use a tAggregateRow and sum by "key" column.


The tricky thing here will be working out the "key". To do this you need to know when the "key" needs to remain the same and when it needs to be incremented. You can do this following this tutorial I put together (https://community.talend.com/t5/How-Tos-and-Best-Practices/Compare-row-value-against-a-value-from-th...). What this does is make use of a really useful feature of the tMap which allows you to store values between rows using tMap variables. 


I hope this helps



Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.