Hi,
I have a table that I want to pivot but without doing aggregation of some sort. The input looks like this:
Well Value
A01 1
A01 2
A01 3
A02 4
A02 5
A02 6
From this I want to generate:
A01 A02
1 4
2 5
3 6
So the Well IDs should become new column headers for columns containing all original values for the specific wells.
Any tips appreciated!
Hi @evert.homan -
What about this? The key is aggregating on Value as a list in the Pivoting node, and then ungrouping that list. I guess you would want to do some cleanup on the column names too.
Hi Scott,
In the Pivot node the Well ID is the Pivot column, but what do you group on? The node doesn’t work if you don’t define the grouping.
Thanks/Evert
No grouping is required. KNIME will give you a warning message about that, but you can still execute the node so long as a pivot and aggregation is specified.
Yes, you are right, got it to work!
If you set the Column Name to Pivot Name under the Advanced Settings at the bottom, the proper column headers are generated so no renaming is required later on.
Much appreciated!
That’s a good tip about the advanced setting. Glad everything worked for you!
Hej Scott, if I can pick your brains some more:
I tried to implement this further to be able to write subsets of data to different sheets of the same Excel file, but the flow stalls at the Pivoting node after the first iteration. It works fine in the bottom branch for just one data subset. I want to use the subset column name to name the different Excel sheet, hence the variable connections.
Ollie_03.knwf (25.8 KB)
Use a regular Loop End.
How do I connect it?
With line from ungroup. Ungroup will be connected to two nodes loop end and sheet uppender.
Pivoting node still stalls…
The Pivoting node just doesn’t seem to work within a loop. Any ideas why?
Hi @evert.homan -
I have a mockup workflow in which the Pivoting node is working fine within a Column List Loop using some fake data. I’m not able to test your workflow since the data is not included (probably because it’s proprietary).
Do you have a dummy dataset that produces the stalling Pivoting node - using your workflow - that you can upload?
Here is the current workflow and input data (I limited it to 10K rows to reduce file size):
Pivoting_in_loop.xlsx (393.4 KB)
Pivoting_in_loop.knwf (16.1 KB)
Hi @evert.homan -
Here’s what I did. The Pivoting node was throwing errors because it was trying to transpose on columns that didn’t exist in various iterations of the loop. So I parameterized the Pivoting node with a couple of flow variables to get around that.
Pivoting_in_loop_SWF.knwf (935.2 KB)
Cheers for this, Scott, much appreciated!
Best/Evert
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.
