Mimicking Excel Pivot Table behavior in KNIME

Hello all

A next question from a Excel transplant.
When I use Pivot Tables in Excel, I can double click on any entry in the aggregation table. This will open another sheet with all details behind that aggregation listed.

For example in a table that contains dates for one year including the corresponding weekdays, I can create a pivot table that aggregates the sum of weekdays per month:
Group: Weekdays
Pivot: Months
Aggregate: Sum of weekdays per month (e.g. “5” for 5 Mondays in June, 4 for 4 Tuesdays, 4 for 4 Wednesdays, etc…)

When I double click on one of the aggregate results (e.g. on the 5 at the intersection of the column of June and the row of Mondays) Excel will create a new Spreadsheet and show me a table, similar to the original data table with all datasets for Mondays including all the other data from the original table (for instance the actual date for each Monday, which is not displayed in the pivot table).
This is great to check the data behind an aggregated date; my actual data is financial data and I use this to see what the actual bookings behind a total amount are…

I managed to get a pivot table working in KNIME.

How can I create a list with all entries behind each aggregated value?

I figured I could do this manually with several parallel operations and a bunch of filtering, but there has to be a more elegant way (preferably without programming Java or so - I can’t code…) - especially since in the original data that I am organizing I have more than 18 groups and 4 pivots and my lifetime is limited…

Hope someone understand my convoluted question and can help me solve this!
Any support is highly appreciated :slight_smile:

Hi @Cal_Steffen ,
After reading your question, the first node that comes to my mind is the GroupBy node, but I would still like to see some dummy data and the expected output to be very sure.

Could you please provide us with that? Even the screenshots with dummy data and expected output would help us better understand the problem.

Thanks,
Sanket

3 Likes

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