# Grouping of the values in column A and the different values in column B as separate columns in the output

Hi!
I cannot find a solution to the following problem. I am still new to KNIME but it should be easy…

I have:

A B
Val1 1
Val1 2
Val1 3
Val2 4
Val2 5
Val3 6

I want as output:

A B_1 B_2 B_3
Val1 1 2 3
Val2 4 5
Val3 6

i tried with a combination of groupby-node and pivot-node but i cannot get it to work.
any ideas?

Thank you!

How do you know which of the values in column B should be shown in B_1, B_2 and B_3? It feels like you are missing a column here.

If you have a column C that indicates B_1, B_2, B_3 etc for each row it becomes easily solvable with a pivot node (Group column A, Pivot Column C, Appropriate Aggregation Method for column B).

If that column does not yet exist I think in a first step you need to create the business logic - probably best with Column Expressions Node, to create that column C.

Sorry i cannot follow.
Column A has doublettes, in my example “Val1” could be “Martin DDD”.
So i would have three rows in which Column one contains your username.
But each of these rows would have a different value in colums B, in my example “1”,“2”&“3”, lets say these are different emailadresses you use.

The resulting output i need would be
MartinDDD for columA ; emailadress “1” for columB1 ; emailadress “2” for columB2 and so forth.

If i just use the goupbynode, group by columnA and manualaggregate by “list” or “unique list” (i think its called) i get the result i want, only the splitting into different colums is missing.

Hope that helps to clarify

if you rank, grouped by A, then unpivot you can build the “B” column names and then repivot…

(Column Aggregator could of course be replaced by String Manipulation, or other mechanism to join the strings if preferred)

btw, welcome to the KNIME forum

4 Likes

I think I misunderstood what you were trying to achieve - you simply want to create columns for every entry for the same Val. That still requires the logic I was talking about to create a third column you can then use to pivot . @takbb made it work nicely I see

1 Like

@mcpizzaburger
Just to add my 2 cents
If you group your data using groupby aggregate with List and then use split collection column you should be good to go too. Just column renaming would be required afterwards
br

3 Likes

Very neat @Daniel_Weikert.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.