Joining rows with the same unique reference

Hi all,

I have a KNIME workflow in which I am concatenating data. I need to have line items with the same unique reference appearing on the same line but I’ve tried all manner of Join, GroupBy and concatenate options and can’t quite get it.

Example - in my screenshot above, the reference 80318254-91831071 appears twice because there is data in both of the subsequent columns. I need these to be combined into 1 row.

80318254-91831071 84 6.3

Any guidance you can provide would be greatly appreciated.

Thanks very much.
Pete

It seems to me that this is a job for the joiner node. If you’ve already tried a Joiner node, how were you configuring it, and what exactly was the result?

Can you provide some data for us to work with?

1 Like

@taylorpeter55

The concatenate node https://nodepit.com/node/org.knime.base.node.preproc.append.row.AppendedRowsNodeFactory?numWorkflows=999999 is adding lines from two sources. So you will get one list from two sources as you have described.
As mentioned by @elsamuel the joiner node Joiner — NodePit will do the job of matching the lines by the key.

2 Likes

Looks like you want a Full Outer Join. As others have already mentioned, this is a job for the Joiner node. If you’re using an older version of KNIME, the crucial setting is “Full Outer Join”. In the overhauled Joiner node* you want to include everything into the output (see my artwork for reference).
If you also merge the join columns, then you’ll not end up with duplicate ID columns that are only partially filled.

*you can tell them apart by the number of output ports. The old one has one, the new one has three


joining rows with the same unique reference.knwf (19.3 KB)

3 Likes

@Thyme @elsamuel @knimediger

Thanks so much for your replies.

I went back to my data to try and further understand what I expected the output to be. Following this, I’ve used the Full Outer join (that i had previously tried), and it does seem to work correctly.

Thanks.
tp

2 Likes