Flow Variables for Column Filter and Loops

Hello,

I have a database with 4 columns in them

In the first column I have a question number and responses from various people in the next 3 columns.
image

I want the output table to look like this

image

First I read in the raw table and using the column filter node pick the first column (i.e. question) and then column1. Then do another flow that takes the question column again and then column2 and concatenate it. And keep doing this until I get the final result. This becomes unworkable if you have many responses. I think the way would be to use a loop that has a list of column names (e.g. column1, column2, column3, etc.) and iterate to the final column and pass the column names to the column filter node using a flow variable. After trying to figure this one out and failing, can someone help me out? Should I use a quickform node? If so, which one? Or Rule Engine Variable node? Which loop nodes should I use (i.e. start and end nodes)

Any simple example to help me get going is appreciated.

Thanking you in advance.

Hi @harveylim,
this data has the structure of a pivoted table, therfore i would use the unpivoting node and afterwards the row filter node to remove empty anwers. Maybe the if you enable ‘Skip all rows containing missing cells in selected value column(s)’ you don’t need the row filter node. But i never had this case and therefore no experiece how it exactly works.

BR
Hermann

3 Likes

Here are 2 approaches, one that makes use of a Column List Loop, and another that uses Unpivoting:

4 Likes

Thank you Morpheus, the unpivoting nodes worked perfectly in combination with the column filter, the column rename and rule-based row filter nodes. :grinning:

2 Likes

Thank you elsamuel for your suggestions, unpivoting worked great! :smiley:

2 Likes

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