Aggregate entries by split values in one column

KNIME Analytics Platform
1

Hi all,

I have a table (Table 1) like:

Column1 Column2
P1 G1;G2;G3
P2 G2
P3 G3;G4
P4 G4

I want to split rows of Column2 and then group by column1 rows by matching split column2 values.

The output I would like to have is following:

Column2 Column_new
G1 P1
G2 P1; P2
G3 P1; P3
G4 P3; P4

I decided to set a workflow by using;

1) cell splitter to split Column2

2) Unpivoting the table

3) Using missing value rows exist due to unequal column size when Column2 in Table 1 is split.

4) GroupBy node usage on Column2 in Table1.

5) GroupBy by Column2 in Table1 and list Column1 in aggregation option.

6) Using Table row to variable loop start on output of step4; extracting matching lists in step5 to each variable.

Is there a better way to solve this problem? Are there any pitfalls of my suggested solutions?

I can also post the workflow if it is asked.

My suggested solution is still running and table 1 has 559454 rows in initial form. Splitting and looping steps take so long. There should be a faster solution I suppose.

Thanks in advance.

Canan

 

2

Could not steps 2) - 5) could be replaced with an Ungroup?

3

Thank you. Ungroup is faster :)

Now I have a table as following:

Column1 Column2_[0] Column2_[1] Column2_[2] Column2_[n]
X a ? ? ?
Y a ? ? ?
Z a b ? ?
K a b c ?
T b c ? ?
         

I used unpivoting node to by keeping Column1 as retained column (enforced inclusion) and Column2[0] to Column2[n] as value columns(Enforce exclusion on Column1).

My desired output is below and I have it now :)

Column1 New_column
X a
Y a
Z a
Z b
K a
K b
K c
T b
T c

Thank you so much for your help.