Transpose with multiple sheets without duplicating columns

I am trying to use the Pivoting node to convert rows -> columns without duplicating / adding suffixes to the new

columns. I am working with multiple spreadsheets acting as forms, ie. each respondent is presented with questions (listed as rows) and responds with their own response. I want to convert the questions to columns and have each response listed under the question, but my problem is that KNIME is insisting on creating a unique Row ID for each question, though they are identical. Any ideas?

Did you try to include Transpose in the loop?

How would I do that? Would you mind including an explanation of how I would rework the workflow?

Hi there @madisonmrivers,

welcome to KNIME Community!

Have you had any progress with your task? Seems to me (from your printscreen) that Loop End node is failing in your workflow and there is no Pivoting node? Is it correct picture?

Anyways maybe your questions are recognized as row ids in Excel Reader and as they need to be unique that is the problem. But that is only a guess and from experience adding data input/desired output usually helps in this cases to find a solution faster :wink:

Br,
Ivan

Hi there -

I am trying to combine several versions of the same Excel form so that the column structure is permanent with the information added underneath (my data input). Questions in the Excel forms are listed in one column, with responses in the next column. My desired data output is an Excel form is a table where each question is listed as a column header with the corresponding responses below from every form.

The unique column headers are proving to be a problem, and I have to arrange hundreds of forms in the way I described above. Any help would be appreciated!

Screen Shot 2020-07-16 at 4.22.46 PM

Hi @madisonmrivers,

you can use RowID and Transpose node between Excel Reader and Loop End node. First node use to move questions to RowID column and then Transpose node will give you form you want and you’ll have your questions as column headers. Give it a try! If you would like a workflow example share two Excel files (dummy answers works as well) and I can make you one :wink:

Br,
Ivan

/DUMMY1.xlsx (8.6 KB) DUMMY2.xlsx (8.6 KB) DUMMY3.xlsx (8.6 KB)

Hi @ipazin,

Thank you so much - I would sincerely appreciate you building a workflow for me! You are a lifesaver!

You could take a look this workflow that hopefully does what you want:

1 Like

Screen Shot 2020-07-20 at 9.59.35 AM

I got these results instead - how did you get all of the answers instead of just one sheet looped?

Hi @madisonmrivers,

you probably need to check option Remove selected column in RowID node.

But you can download @mlauber71 workflow and check for node configuration for yourself :wink:

Br,
Ivan

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