concatenate two columns in one table, collate identical entries

KNIME Analytics Platform
#1

Hello again,

I have a table in which two columns have partly identical cell entries (strings). In other rows only column 1 has entries, in other rows column 2 and in other rows column 1 and 2 have different entries. I want to merge both columns and the new generated column 3 has

1) only one row-entry, when column 1 and 2 entries are identical

2) two comma-separated entries, when column 1 and 2 are different

3) one entry, when only column 1 or 2 has an entry.

 

How do I manage that? The Column-aggregator-concatenate does not fully manage this job, it creates dublicate entries and sets "?", where one column was empty.

Thanks in advance,

Rockbar

#2

Have you tried Joiner with Full Outer Join?

#3

Joiner is not the correct node, since it works with two different tables and it does not bring together different entries in one cell (with e.g. ";"-separated).

I could leave out the question arrows by unticking the "missing"-field in the concatenate-command in the column aggregator. Thus point 2) and 3) in my first thread-wishlist are full filled.

But still, identical entries are not merged to a single entry (point 1) ).

 

#4

Edit:

ignore the the java snippet solution, I include in case it is useful for something else. But you can do this with the column aggregator. 

1) In the columns panel select your columns

2) In the Options panel choose: Unique concatenate

------

Here is the other solution:

Here is a java snippet that will do what you want:

String first = c_column1;
String second = c_column2;

String output = null;

if(first != null && second != null)
{
	if(first.equals(second))
	{
		output = first;
	} else
	{
		output = first + ", " + second;
	}
} else
{
	if(first != null)
	{
		output = first;
	} else
	{
		output = second;
	}
}

out_combined = output;

 

You could also do this in a rule engine node I would assume. I've attached a 2 node workflow showing how this works. You would need to replace:

String first = c_column1;
String second = c_column2;

with your columns. 

Cheers

Sam

 

1 Like
#5

Super. Thanks a lot. Just one click away. One last additional question: Is there a possibility to switch to case-insensitive? Currently the Unique Concatenate works case-sensitive.

#6

Doubtful, you could convert the columns to lower/upper prior to using the aggregator?