Optionally merge row to previous row if conditions apply

[Anonymous]

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.

Thanks

[Anonymous]

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

A	B	C	D	E	F	G	Key
3	4	5	2	3	4		1
1	2	6	3	7	8		2
7	3	6	4	3	5		3
98	43	67	5	4	3		4
						2	4
2	5	5	6	3	3		5
						65	5
						9	5
2	4	3	5	4	4		6
						23	6
						4	6
						5	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

[tale103108]

Great response to this question.  And I have a simple key in the data,

Any chance on you providing a job screen shot as an example?

Thanks!

[Anonymous]

I would love to be able to do this, but I wrote this over a year ago and do not think I have a job that matches this requirement. I will have a look and see what I can find. In the meantime, do you have any specific questions on how to implement this?

[tale103108]

No question at the moment.  But that could change once I get into it.  

My data is like so (partial list):

Date State City Temps Day1 Day2 Day3 Day4 Day5 Day6 Day7

4/1/2019,AZ, Phoenix,98,,97,,,,

4/1/2019,AZ, Phoenix,,,,,95,,92

and I want

Date State City Temps Day1 Day2 Day3 Day4 Day5 Day6 Day7

4/1/2019,AZ, Phoenix,98,,97,,95,,92

[Anonymous]

That looks l simple enough. Just remember that you have a composite key there (date, state and city), other than that it looks pretty straight forward.