Need a support to prepare a pivot table report

Hi Everyone,
I need a support to prepare a report like pivot table. I tried using pivoting node. But I couldn’t set the node properly. Can anyone provide me any suitable solution?
I added calculation column in Manual Aggregation tab. But I am a bit confused what I have to provide in pivots and groups.

Items: A,B,C,D,E
Decision: yes, no
possibility: High, medium and low
First of all, I need to count and display decision and possibility column separately for 5 items. After that, I need to calculate sum of each column along with grand total (15 for decision and 15 for possibility)

I have attached demo data along with required table.

Required table:

To have a calculation in Pivot nodes you need to add column to your data with constant value, say 1. And use this column in Manual aggregation tab.

1 Like

I didn’t get it clearly. Can you show an example?

As @izaychik63 said, the Pivoting node requires you to have a column on which to pivot, and another on which to aggregate. In your case, you’ll need to add another column.

Or you could use 2 separate pivots:

  • in the first, group by Items, pivot on Decision, and aggregate on Possibility using Count
  • in the second, group by Items, pivot on Possibility, and aggregate on Decision using Count

Then, join the outputs of both pivoting nodes to get a single table.

If you want column totals, then you can concatenate the first and third outputs of each pivoting node to attach the column totals, before doing the join.



Concatenating a row of grand totals is left for you to work on.

Your desired output table is formatted to be a human-readable spreadsheet. You might be able to approximate it in a KNIME table, but this isn’t really what KNIME is for.


Thank you @elsamuel for your solution. It’s clear to me now.
In the mean time, I tried with group by node. It also works.
test.knwf (15.0 KB)

I need another support. Is it possible to add another row counting the total decision and possibility data? (yellow marked column row)

Hello @emshihab,

It is. Take a look here:

Keep in mind that KNIME table output is not like spreadsheet and cells can not be merged.


Thanks a lot @ipazin . It worked

1 Like

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