How to Pivoting and sorting data?

Hello together,

I need some help in this case…

As in input i have a table with Values devide by “:” like in the first picture with Month:Count
Columns bevor 13 can be empty but don´t have to and columns between can be also empty.
I have tried it in different ways but don´t get the solution… Can you help me to get the second table as output?


Thanks a lot!

Hello @KienzlS,

and welcome to KNIME Community! This is approach I would take:

  1. Use Missing Value Column Filter node to remove column with all missing values
  2. Loop over all columns with Column List Loop Start node where in each iteration you will use Cell Splitter to split month from it’s value
  3. End it with Loop End which will concatenate results from each iteration and follow it with Pivoting node to get format you need

Here is screenshot of my flow:

Additionally I have used Extract Column Header node to have same column headers in each iteration and Table Manipulator at the end to filter and sort columns. Alternatively (not using loop) you could get all column in one column using Unpivoting node and then follow same approach as already described.

Hope this helps and if any questions/comments feel free to ask!

Br,
Ivan

2 Likes

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