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.

 

Data.png

 

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.

 

Thanks

Highlighted
Community Manager

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

 

ABCDEFGKey
345234 1
126378 2
736435 3
984367543 4
      24
255633 5
      655
      95
243544 6
      236
      46
      56

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

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch