Creating new columns out of a character separed list

Hi, I have a semicolon seperated list. The list contains teams which work on a topic. I want to create new columns for those teams like one to many node.

For example my input table contains 2 coloums:
Item teams
ID1 team1; team2
ID2 team2; team3; team4

The output should be:
Item team1 team2 team3 team4
ID1 1 1 0 0
ID2 0 1 1 1

Any solution hints?

Hi,

I was able to solve your problem with a Cell Splitter, an Unpivoting and a Pivoting node.
Attached is a little workflow that solves your example.

Please let me know in case you have any questions!

Cheers,
Kathrin

Character_Separeted_List.knwf (11.1 KB)

2 Likes

Kathrin, I played with your example and found that Group by with no group and Unique concatenation works strange.


The result shows team 2 two times
image

Is it correct?

Hi,

yes, this is the correct behaviour of the node. As you haven’t defined any groups it makes just a unique concatenation of the different string values, separated by commas. In your example this is the string value in the first row comma separated by the string value from the second row. (one string value here is the complete cell value, not only team1)

Cheers,
Kathrin

1 Like

Kathrin, could you please explain what unique means in this case? I expect a list of every value collected just ones.

Each string in a cell is one value. This means the whole string “Team1; Team2” is one value and this is already different to “Team1; Team2; A”.

Here a little different example, to make it hopefully a bit clearer:
RowID Value
1 A
2 B
3 AB
4 A

The unique concatenate would output: A, B, AB
But not an “A” in the end, as this is equivalent to the first entry in Row 1.

Does this makes sense?

Thank you, Kathrin. Now I see.