Can KNIME de-pivot / work with a pivot table?

Hi there,

I am new to using KNIME and am wondering if KNIME can work with a pivot table? I generated a pivot table with Excel, so the rows with a value > 1 need to be duplicated in order to get a flat table. Can KNIME do this?
I am sorry if there is already an answer but I really couldn’t find one.

Best regards,

Hi, @MTAB01

Welcome to the KNIME Community Forum.

Please help us to help you.

If you give an example with data, we will be able to help you better. It is also important to clarify what you want.

The more specific you are and the more context you give, the more help you will get.


1 Like

Thanks for the quick answer.
I have a table like this:
Attribute 1 Attribute 2 Value
Apple red 1
Banana yellow 2
So there is the information that there is one red apple and two yellow bananas in two rows. I think it is neccessary for KNIME to have a flat table with three rows: one for the red apple and two rows with each a yellow banana. Can KNIME work with this and duplicate the yellow banana so that there are two banana-rows or do I have to do this myself?
Or can KNIME work with the above table and “understand” that I have to datasets for banana?

Hi @MTAB01 , welcome to the KNIME community.

I think I understand where you are coming from but if the value 2 is originally derived from the sum of two rows each containing 1, then KNIME can’t know this. The source data at the lowest level of granularity would be required unless you can subsequently write some rules to “build” the data. There are ways to build data based on duplicating existing data such as the “one row to many” node(which may be of assistance here but i can’t tell from the brief example) but there isn’t really a generic way of achieving this.

If you only have summarised data then effectively you’ve lost information. Suppose the “2” were actually 100 and the original source data were 25, 25 and 50… How could KNIME infer this?

Edit… Having reread your original question alongside the example maybe the One Row to Many node is a possible solution if the “2” simply represents the number of rows required :slightly_smiling_face:

If you have a pivot table in Excel, then normally you’d have the raw data from which that pivot was derived. It would generally be better to supply that raw data to KNIME and then work from there. Excel pivot tables and KNIME pivots are very different things.

[btw. I’ve moved your post to the “KNIME Analytics Platform” section as the “Node Development” section of the forum is more about the technical details of the creation of new nodes, rather than general KNIME usage,
so you’ll likely get more responses in the more general area].

Thank you very much, I get your point. I don’t have direct access to the raw data in this special case, but I will try the One Row to Many node, the “2” does represent the number of rows required. Thanks again :slight_smile:

1 Like

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