File reader: data belonging together spread over mutlple rows

[marcv]

Using the file reader node my resulting table looks like this:

Blockquote
||name|total|information total|subtotal|information subtotal|
| — | — | — | — | — | — |
|row1|John Doe|1000|deliveries|empty|empty|
|row2|empty|empty|empty|250|delivery 1|
|row3|empty|empty|empty|250|delivery 2|
|row4|empty|empty|empty|500|delivery 3|
Blockquote

Subtotals are being put on a new row and there’s no common key. I want to combine the rows like this:

Blockquote
||name|total|information total|subtotal|information subtotal|
| — | — | — | — | — | — |
|row2|John Doe|1000|deliveries|250|delivery 1|
|row3|John Doe|1000|deliveries|250|delivery 2|
|row4|John Doe|1000|deliveries|500|delivery 3|
Blockquote

I’m stuck and could use some help merging the rows. What’s the best approach here?

[mlauber71]

In for some seriously over engineered flick … here you go.

We determine the start and stop lines of the name groups and use them with SQL power of joining over ranges.

OK now someone can point out how to do this with Moving Average or smth.

kn_example_subtotals_shifted.knwf (103.5 KB)

[marcv]

Wow…
What a fast and impressive response!
Thank you very much.

In my case the file being read with the file reader has rows in the correct order. In this case i discovered i could use the missing value node. Settings of this node enable missing values to be replaced with the value of the previous cell in the same column. I used this to fill the missing values of the subtotalrows.

[mlauber71]

Ah that was what I was missing That is a better solution. I was searching in the wrong place and had hoped to have made a mental note about using that