Transpose row to column and column to row

Hi @ArthurMundim

yes this work for repeated values. Because I do use the row Id in the pivoting node. This does ensure that the original data table structure is recreated.

Best wishes, Iris

Hi @Iris,

I reopen this this topic because I have a question about the Unpivoting Node. I have a table with two columns. in the first column are the labels and in the scond there are the values. This Looks like this:


You can see that there are dublicate values in the lable column but unique values in the value column. I want the unique values in the pivoting table in the column [JOB]FaultMemReportMirrorMemoryDTCByStatusMask.DTC so the result should look like this

[JOB]FaultMemReportMirrorMemoryDTCByStatusMask.DTC
U140900
U012820
U140A00

This Job I have to do for all the row in my two column Input table. So how do I have to configurate the Unpivoting Node and the Pivoting Node?

Thx and greetings,
Brotfahrer

Hi there @Brotfahrer,

if I got you right you don’t need to do any unpivoting but only use Pivoting node with no group columns, Label as pivoting column and Value as aggregation column with aggregation method List. Then use Ungroup node. That should do it :wink:

Br,
Ivan

4 Likes

Hi @ipazin,

perfect!

1 Like

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|Year|Month|Type|Value|

|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!

Hi and welcome
Looks like an unpivot operation. Have a look at that node.
Just to let you know. It’s always best to upload a sample file (excel, csv,… attached)
br

KNIME.xlsx (44.6 KB)

Thank you very much! I’ve thought about “groupby” and then “unpivot” by not sure how to get the data values correct for each line item - below I uploaded the file, would really appreciate if you could have a quick look at it. BR Fredrik

Hi @fredrikt

As @Daniel_Weikert suggest, go for the Unpivot node. Configure it like this


gr. Hans

Thanks :slight_smile: But this wont solve it - I will have all values in one column, but not split by type net sales/cogs/other income?

Hi @fredrikt

to make the unpivot node work. I made some changes to the Excel Reader configuration, so I would have a “normal” data-set.
Schermafdruk van 2021-09-04 08-55-55
The outcome of my workflow looks like:
Schermafdruk van 2021-09-04 08-52-30

See the workflow: transpose.knwf (37.7 KB)

gr. Hans

2 Likes

Got it! Thank you so much - you are an angel!

Regarding column “Year” and column “month” which is now in separate columns - is it possible to merge them info a single column called “date” and instead of the format 2021 Jan, 2021 Feb, 2021, Mar - show something like e.g. Jan-21. Feb-21 and Mar-21?

BR

Fredrik

2 Likes

Hello @fredrikt,

you can use join() and substr() functions from String Manipulation node to transform 2021 Jan to Jan-21.

Br,
Ivan

4 Likes

Hi @ipazin , this is really helpful.
But I met some problem during the final execution, execution failed and inform

Execute failed: Java heap space: failed reallocation of scalar replaced objects

Is there any method to fix this?

@Kenyx maybe you could explain further what kind of operation you wer doing and what your system is like. If you have costly operations you might want to take a look at these discussions:

3 Likes

Hi @mlauber71
I am pivoting a large mount of unpivoted datasets, my config of memory is set up to 8G, when I executing the whole program is flashing and the Windows taskbar can see multiple knime program open and close.

@Kenyx maybe you open a new thread with some more details about your system and your data. Transpose can be a costly operation. 8 GB might not be that much.

Hi @Kenyx

Did you solve this problem ? If not, I would be happy to help with it if you open it in a new thread as suggested by @mlauber71 :slightly_smiling_face:

Best wishes,

Ael

2 Likes

Hi @aworker ,

Thank you.
I already solved this problem, I made mistake on pivots and aggregation method.

BR
Ken

2 Likes