Convert rows to columns , but columns have a suffix

[MarekV]

Hi,
I can’t help myself with the following problem in KNIMA.
I have a table which contains for example 5 columns.
One column is ID and the remaining 4 are values.
If the table will have 4 rows so i need to multiply the number of columns times 4.
Because duplicates would arise so I need each column to have a suffix where it would be clear in which order the column is.
See the example in the attachment.
It’s NOT Unipovitng nor Pivoting in my opinion.
It should be done in a loop but I don’t know how.
Of course there are millions of records in the table .
The ID is repeated 4 times each time.

I found a thread ( Collapsing rows into a single row with additional columns - KNIME Analytics Platform - KNIME Community Forum) that shows how it is solved when there is a unique ROWID in my, case it is not a unique ID, that is why I want to put it in one line.

sample data KNIME.xlsx (12.9 KB)

[armingrudd]

Hi @MarekV,

You can still use the solution provied by @HansS in the mentioned topic or also you can use a GroupBy node to concatenate the values and then split the values with a Cell Splitter node inside a Column List Loop Start and Loop End (Column Append) loop.

[MarekV]

Hello,
I’ve worked on it a bit and you’re right , it just needed a bit of work.
The bottom line was that I need to use two different cycles in my opinion and via rowID create a new identifier which will just represent the newly created record.
Because it was 7 million rows, I had to sort it before the transfomation so it wouldn’t affect the performance

[armingrudd]

If you use the GroupBy and Cell Splitter nodes, you won’t need an extra loop.
You can use the ID as the grouping column and concatenate the values in the selected coulmns and then use a Cell Splitter in a loop to split the values in columns.

[MarekV]

Hello,
I didn’t quite understand how your design works.
I finally took the plunge and after a few tries I was able to get it running.
GroupBy has to be set up correctly because “set” and “ist” don’t work the same.
Ok cell spliter doesn’t work in this case but Split Collection column works beautifully .
Splitting Multiple Collection Columns – KNIME Community Hub
My solution did work after three hours but I still had to redo the object types because it all turned into text.
Now it took 50 minutes .

But otherwise thank you on the recommendation managed to get it to run eventually.