Transposing Columns

[harshadbarge]

I am trying to transpose selected columns but I am unable to do so.
Here is the difficulty where I faced:

1. Why are the columns renamed with suffix (_1); Makes it so much harder to group because of different names
2. How do I rename a column with the original as a reference? I am getting an error when I join the column header. (either i am not getting what it is supposed to do or how)
   Input Table:
   Input.xlsx (19.9 KB)
   Expected Output:
   Output.xlsx (25.8 KB)
   Knime Workflow:
   Leagues Clubs.knwf (9.0 KB)

[Snowy]

Hi @harshadbarge
KNIME is renaming the column because you can’t have columns with the same name and expect the program to do what you want logically. (E.g. you know that there is a FY 19 Gaming Rev and a FY 19 Catering Rev, but KNIME just sees FY19.) I would recommend instead of having a 2 header approach, have a single header with column names such as FY19_Total, FY19_Gaming, FY18_Gaming, FY18_Bar, etc… This will make life a lot easier I think.

You can rename columns by using the Column Rename or Column Rename (Regex) nodes.

And for your actual issue… I don’t have time to fully solve it, but I think some combination of an Unpivoting and Pivoting, might help solve what you’re trying to do, based off the expected output.

From here you could filter out the ‘ugly’ data (e.g. rows with 0 values) using the Row Filter node.

Hope this helps get you on the right track!