[solved] Using flow variable in Pivoting node

[gbaudry]

Dear KNIME community,

I would like to know how to use the flow variables in the pivoting node? It is not straightforward to match the standard setting to the flow variable tab. At least, I need to overwrite the aggregation setting - Column to consider.

Thanks in advance for your help
Cheers
Gino

[armingrudd]

Hi @gbaudry and welcome to the KNIME community forum,

In the Flow Variables tab, you can find “aggregationColumn” category where you can set your desired configurations. Just notice you first need to add any number of columns that you need and then you can change them by flow variables.

For example, if you add a column for aggregation, under the category I mentioned, you can find the index number of 0 for the column for each option like column name. So if you provide a different column name for this index, the aggregation column will be changed.

[gbaudry]

Hello @armingrudd

I am sorry I do not follow your tutorial. I tried your commands but there is always a warning. I created a string flow-variable (which I called pivot-selection):

column1[unit]| colum2[unit]|…

for which the number of columns can change from one country to another (I have a loop per country). So, I want the selection of column to aggregate to be overwritten by the flow variable. My pivot is always the same.

Is this what you were talking about (figure)? Do I have to add the maximum number of columns in the manual aggregation tab even though I am overwriting it? Where do I put the aggregation method? Do I write manually concatenate in the aggregation operator setting cell?

Thanks again

Cheers

[armingrudd]

The “pivoting-selection” variable must contain the name of the column on which you want to apply the aggregation method and it can be changed in each iteration of the loop.

I think this blog post can help you to understand how flow variables work in KNIME:
https://blog.statinfer.com/knime-spirits-at-your-service/

[gbaudry]

Thanks. It clarifies the use of the empty cells that I did not used before, but not the rest. Is there somewhere a flow example to download in which a pivoting node runs on flow variable?

Cheers

[armingrudd]

flow_pivot_example.knwf (32.4 KB)

In this example, I have used flow variables to apply “Mean” aggregation on “column1” in the first iteration of the loop, “Concatenate” on “column2” in the second iteration and “First” on the rest.

[gbaudry]

Thanks a lot, now I get it, and everything is working fine. Two things I would add in case other people are struggling too:

1. That also helped me a lot to understand the flow variable config: How to control GROUP BY NODE using flow variables
2. What I did not get is even though you overwrite the setting, you need to set (in part) the standard manual tab before setting up the flow variable tab.

Thanks a lot for your support again

[ipazin]

Hi there @gbaudry,

welcome to KNIME Forum and glad you made it work!

I see you have added [solved] to the topic title. This is ok but preferred way is to mark reply that helped you as a solution. Solution button is behind 3 dots…

Br,
Ivan

[gbaudry]

Thanks for the tip. I will remember it for the next time.
Cheers