How to Unpivot Table - Transpose the Monthly Headers from column to rows

[Heldyyyyy]

Knime problem unpivoting.xlsx (61.3 KB)

I am having a hard time fixing this table, the raw data shows that all monthly are in the column headers, it should be transpose to rows for betting understanding.

[Daniel_Weikert]

Hi
there is an unpivot node in KNIME. Have you tried it yet?
br

[Heldyyyyy]

Hi @Daniel_Weikert , I already tried it but am experiencing another group problem. I don’t know how to combine these 4 columns as one column only, Unpivoting and group by doesn’t work, do you have any idea how to do this? what node should I apply to?. Btw, thank you for replying and seeing my post, appreciate it.

[Heldyyyyy]

Here’s my current workflow

[Heldyyyyy]

The q1 profit of 563 should just appear in month of jan, feb and march while the q2 profit of 1417 should appear below the q1 profit and align in the month of April, may and jun… I need to have one Quarter profit column which indicates the profit per quarter in a row format instead in the column so that it will align with its month and quarter. I try different nodes but I just can’t successfully load it.

[HeatherPikairos]

Hi @Heldyyyyy

Now you have the table after using the -Unpivoting- node, you could add a -Rule Engine- node and create a new column called “Quarter profit”.

Using rules such as:

$Quarter$ = “Q1” => $Q1 Profit Plan$
$Quarter$ = “Q2” => $Q2 Profit Plan$
$Quarter$ = “Q3” => $Q3 Profit Plan$
$Quarter$ = “Q4” => $Q4 Profit Plan$

Adding the result as a new column

Hope that helps,
Heather

[Heldyyyyy]

Hi @HeatherPikairos thank you for the input, I try the rule engine but shows the same result, the q2, q3, and q4 column should just merge/combine to q1 column (which will be rename as “Quarterly Profit”) and their respective data should be lookup to the quarter column.

This should be the final format of the table. I am still trying other nodes, but doesn’t work, I think I have the wrong process of unpivot in my workflow. I want to ask if this final format is possible to achieve considering my current workflow?

[HeatherPikairos]

Hi @Heldyyyyy are you able to share your workflow either by exporting it or uploading it to the hub? It will be much easier to see what is going on with the unpivoting.

Thanks
Heather

[Heldyyyyy]

Here’s the workflow
Test File.knwf (115.0 KB)
Here’s the excel file
20230706_Plan - Alle Plan.xlsx (65.9 KB)

[HeatherPikairos]

Hi @Heldyyyyy

Thanks for sharing

I have been able to add the -Rule Engine- node to your workflow and believe I have achieved the column you desire:

The -Rule Engine- configuration looks like:

The column can be found at the end of the file but looks like:

Here is the workflow:
20230712 Pikairos Test File - Unpivot and Rule Engine.knwf (414.0 KB)

Let me know if that solution works for you

Thanks
Heather

[Heldyyyyy]

Oh my, it works!!! I followed your answer last time with the rule engine but the output is not the same, I will check where I input wrong. Thank you @HeatherPikairos for this! I was having a hard time solving this one since I’m new to the software and unfamiliar with some nodes. Thanks again for the big help!

[HeatherPikairos]

Hi @Heldyyyyy Happy to help Keep learning KNIME, its a great software! Over time you will get to know the most commonly used nodes and see how you can apply them in many different ways!