Join rows based on second column values

[mcrisnidh]

Knimers,

Im struggling to find a way to multiple rows into a single row based on a second column’s value.

In the following image you can see columns Col0 and RM Mark

The second column “WO” values mark how the rows of the first column should be joined in a single new row, example: rows 201, 202, 203 and 204 must end up being a single row, and repeat the same process every time a WO starts.

Thank you in advance for all your suggestions. Every bit of help is truly appreciated.

[ArjenEX]

Hi @mcrisnidh

This is tricky in its current form since the RM Mark column has a generic value. I recommend amending it in such a way to create unique groups.

Below is one way:

Make the RM Mark unique by concatenating the RowIndex when a value is found. You can do this via Java Snippet or Column Expression. In CE, you can use:

if (column("RM Mark") !== null) {
    column("RM Mark") + "_" + rowIndex()
} else {
    column("RM Mark")
}

WO now has become WO_1, WO_5, etc.

Use a Missing Value node and replace any null in RM Mark with previous value. This will supplement the group assignments.

Then use a groupBy node with RM Mark as grouping and use Col0 as aggregation column with a method of choice (concatenate for example).

See WF:
join rows based on second column values.knwf (23.9 KB)

Hope this helps!

Tip: try to include your workflow or attach the source in cases like this. It makes peoples lives a lot easier as it avoids having the re-type your data structure.

[Daniel_Weikert]

you could also apply moving aggregation for a cumulative count for the group identifier

br

[mcrisnidh]

This is so great. I spend hours working on something and then you hit the wall and get stuck, with no new ideas and someone comes with the most amazing solution. Thank you for making it look so easy, and thank you for the information you shared. This worked. Thank you again.