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.
image

Required table:
image

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.

image

image

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.

3 Likes

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.
image
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)
image

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.

Br,
Ivan

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.