Transpose rows / pivoting - structure data

Hi, I’m new to Knime and have a question regarding structuring some data to a “flatfile”.

|Row0|Row1|Row2| Row3| Row4| Row5|
|Store| Year|Month| Net sales| Other income| Sum revenue|
|Store 1|2021|Jan| 100| 50| 150|
|Store 1|2021|Feb| 110| 60| 170|
|Store 1|2021|Mar| 120| 70| 190|
|Store 2|2021|Jan| 190| 140| 330|
|Store 2|2021|Feb| 200| 150| 350|
|Store 2|2021|Mar| 210| 160| 370|
|Store 3|2021|Jan| 280| 230| 510|
|Store 3|2021|Feb| 290| 240| 530|
|Store 3|2021|Mar| 300| 250| 550|

Below is the result i need, how to do? can you let me know details steps?


|Store 1|2021|Jan|Net sales|100|
|Store 1|2021|Feb|Net sales|110|
|Store 1|2021|Mar|Net sales|120|
|Store 1|2021|Jan|Other income|190|
|Store 1|2021|Feb|Other income|200|
|Store 1|2021|Mar|Other income|210|
|Store 1|2021|Jan|Sum revenue|280|
|Store 1|2021|Feb|Sum revenue|290|
|Store 1|2021|Mar|Sum revenue|300|
|Store 1|2020|Jul|Sum revenue|310|
|Store 1|2020|Aug|Sum revenue|320|
|Store 1|2020|Sep|Sum revenue|330|
|Store 1|2020|Oct|Sum revenue|340|
|Store 1|2020|Nov|Sum revenue|350|
|Store 1|2020|Dec|Sum revenue|360|
|Store 2|2021|Jan|Net sales|360|
|Store 2|2021|Feb|Net sales|360|
|Store 2|2021|Mar|Net sales|360|
|Store 2|2021|Jan|Other income|360|
|Store 2|2021|Feb|Other income|360|
|Store 2|2021|Mar|Other income|360|
|Store 2|2021|Jan|Sum revenue|360|
|Store 2|2021|Feb|Sum revenue|360|
|Store 2|2021|Mar|Sum revenue|360|
|Store 2|2020|Jul|Sum revenue|360|

Thanks a lot!

Welcome to the forum @fredrikt.

For this, I’d use the unpivoting node.

  • Value columns would be Net sales, Other income, and Sum revenue.
  • Retained columns would be Store, Year, and Month

Starting data:

Final table after some cleanup:

You can find the workflow here:


It works! Thank you so much for the time you have taken - you are the best! Regarding column “Year” and column “month” which is now in separate columns - is it possible to merge them info a column called “date” and instead of 2021 Jan, 2021 Feb, 2021, Mar - show something like e.g. Jan-21. Feb-21 and Mar-21?



Sure. There are a few ways you could tackle this, and one way is with a String Manipulation node. I’m assuming that all the dates are in the 2000s, and that you don’t need the resulting string as a Knime Date&Time format.

You want to repalce the “20” with “-”, but the String manipulation node only works with strings, and not integers. The fist step is to convert the year to a string with:


Next you can do the replacement:


Then finally, add the month to get the final expression:


If you put the String Manipulation node before the Table Manipulator node, you can do all the cleanup (renaming columns and removing unneeded columns) at the end.


Great! Thank you so much - its worked!

Regarding replacing specific cell values I used “String manipulation” - is it possible to replace more than one expression in each “nood/tool” I used: replace($Type$, “Net sales”, “Nettoomsättning”) and replace($Type$, “Cost of goods sold”, “KSV”) and replace($Type$, “Other income”, “Övriga intäkter”) in separate noods/tools and want to lean a more efficient way of working. :slight_smile:

Hello @fredrikt,

for such replacement I would use Cell Replacer node. But Rule Engine is also fine for such operation.



Hi @ipazin.

I hope all is well.

I have a question regarding some efficient way of index/matching in missing values (A, B and C) in column “Discount Group” based on another column “Customer codes”.
I’ve attached an example below where I have the raw data sheet.

I was thinking of creating a separate sheet “mapping table” where all duplicates are removes and can therefore we used to match each customer code/discount group?


KNIME - Indexmatch mapping.xlsx (14.1 KB)

Hi @fredrikt -

I think the Cell Replacer with this config will get you there?


Hello @fredrikt,

Sounds like a right approach together with Cell Replacer node as demonstrated by @ScottF.



A post was split to a new topic: Excel Writer issue with overwriting existing file

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