Add Row based on filters

Hello everybody,

I am looking for a solution as follows. I am extracting data into a table and group it by Code and period. That is fine and it works. But sometimes I have for some codes not the same number of rows. That is something I need. How can I loop through the table and add a row, to but values into the specific fields.

Here a short example.

Row0 123 2019/01 135 26 0 3
Row1 123 2019/02 137 24 0 3
Row2 123 2019/03 135 26 0 3
Row3 123 2019/04 132 29 0 3
Row4 123 2019/05 132 29 0 3
Row5 234 2019/01 136 25 0 3
Row6 234 2019/02 131 30 0 3
Row7 234 2019/03 133 28 0 3
Row8 234 2019/04 132 29 0 3
Row9 234 2019/05 132 29 0 3
** Row10 234 2019/06 127 34 0
Row11 345 2019/01 131 30 0 3
Row12 345 2019/02 127 34 0 2
Row13 345 2019/03 124 37 0 2
Row14 345 2019/04 122 38 0 2
Row15 345 2019/05 117 43 0 2
** Row16 345 2019/06 116 43 1

period 2019/06 is missing for code 123, which I have to add.

Thanks for your help.


Hi @Andy1981,

what you can do is to build basic table which contains all expected information.
To get this table you need 2 group by nodes to get unique entries for each one.
Therefore you only set the group condition to Code or to period. After that you can use the cross joiner to join both results of the group by node.
After you have done this you can join your main table to the result of the cross joiner.


Hello @Andy1981,

you can try this trick: first pivot your data using Pivoting node and then unpivot your data using Unpivoting node :sweat_smile:


Hello ipazin,
thanks. Understand that idea. But how to unpivoting the pivot table? When I use Mean aggregation I will have no chance to split.


Hello @Andy1981,

in Pivoting node Code column is grouping column, period is pivoting column and use First as aggregation method on all value columns (OK, NOK…). In Unpivoting node grouping column goes to Retained columns list while all other columns go to Value columns list. Make sure to have following column name settings in Pivoting node in order to preserve period values:


Hi Ivan,

okay. Understand. But I don´t come back to the old version. I have following results:
Row ID ColumnNames ColumnValues Code
Row 0 2019/01+OK 135 123

Where are my period and OK, NOK, HIGH RISK column?


Hi Andy,
i would perform following steps
a) group by CODE (use only column CODE in group setting to get unique values)
b) group by PERIOD (use only column PERIOD in group setting to get unique values)
c) use cross joiner node for both previous results to build the full matrix
d) join initial table (left outer join) to cross joiner result (key columns CODE and Period)


Hello @Andy1981,

I see what are you talking about now. I have only tried it with one column (silly me!) while you have multiple columns. In that case you need a bit more nodes to make use of my approach which you already might figured out. In case you haven’t here is workflow example to check out:


1 Like

Hello @ipazin,

Solved this. Thanks for your help.


1 Like

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