Convert a table to another table

Hello all,

Need some help here as I could not figure out how to do this in Knime. I have a source table like this:

image

How can I convert this table to following table?

image

Really appreciate any hints. Thanks!

YH

Hi @yenhung1117.

Did you have a look at this examples?

Br

2 Likes

might not be the best solution but it works:

Workflow Link

2 Likes

Hi @yenhung1117 , I am grateful to @wsturmzfknime for taking the time to produce a workflow and create some sample data.

As a general principle, you are more likely to get assistance faster if you upload data rather than screenshots. This is especially true when the question is about some complex table manipulations. Most of us cannot do these in our heads, and so need to take time to enter sample data first which means often we don’t… :wink:

I have taken the liberty of adding to @wsturmzfknime’s flow because I was not going to write the data myself.

This can then be used with an unpivot followed by a pivot

image

To start with, the unpivoting: we want to keep the Supplier, Product and Date columns where they are because we want to retain these as columns later.

This results in the following table:

After that the pivoting

The rows that we wish to “retain” (go “unchanged”) from the above table are ColumnNames, Product and Supplier:

The column that will form the “pivot” (create new columns for each value) is the Date column

And the column to be used for the tabulated values is the ColumnValues column. Since we just want to populate the table with the existing values, select “First” as the aggregation option, to take the value present in each row.

Note the advanced settings selected, in particular “retain row order”

This results in the following table:

ConvertATableToAnother with unpivot and pivot.knwf (41.7 KB)

4 Likes

Hi @takbb and @wsturmzfknime :

Thank you so much for the very detailed solutions! :grinning:

I promise I will do better next time when asking questions in this community. Truly appreciate your help!

Best Regards,
YH

1 Like

You’re welcome @yenhung1117 :slight_smile:

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