Pivot table

[Markus3003]

Hi all,

I have the attached sample data Sample_Data.xlsx (17.5 KB), and would like to create a pivot table more or less similar to excel (like in the screenshot). I tried with group by, but it did not work so far.

Can someone help me?
Thank you very much!
Best
Markus

[ArjenEX]

Hi @Markus3003

Would something like this be workable for you?

If so, please let me know and I’ll elaborate on a way to approach it in KNIME more in detail.

[Daniel_Weikert]

Are you referring to something interactive or do you want to give the pivot node in KNIME a try?
br

[si_daniel_a]

How to Build Pivot Tables - A Vlog | KNIME

[Markus3003]

Thanks to all of you so far,
@ArjenEX that looks pretty good, can you share how you achieved that?

[ArjenEX]

Sure @Markus3003

Again, this is a way to approach it. Could be potentially done quicker but the impact shouldn’t be that high in terms of required resources to perform this.

Starting point is a pretty straightforward pivot with a group on Zwischengebnis und Projekt. Followed by a Group of Bewegungsart.

Next are some cleansing steps to sort it in the desired order, both in terms of rows and columns.

Calculating the row and column sum is next. Starting with the first, I opt for a Column Aggregator that filters on datatype Double. The aggregation method, as per the Options tabsheet, is just sum.

For the column sum, I opt to seperate this from the main data flow and get it via the GroupBy node. Also including the earlier calculated row_sum to get the overall sum as per your example.

To later merge this with the main table, I equalize the column names to make this easier with a Column Rename Regex node. By applying (Sum[(])([A-Za-z0-9_]+)([)]) it is converted to its original name.

With a Union based concatenation, both are merged together and the final table is created.

WF:
Pivot Table Excel to KNIME.knwf (44.8 KB)

Hopefully this provides some inspiration.

[nan]

Hi, @Markus3003 ,
I expect the nice workflow and detailed description of @ArjenEX to help you in getting started. I then suggest to use the row-wise and column-wise totals that the Pivoting node calculates (see its second and third output ports).
With the sub-totals you can follow @ArjenEX 's example with GroupBy and Concatenate. Here is how I would do it.
pivoting_with_sums.knwf (34.1 KB)

Have a nice day!