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.
row ID
Code
period
OK
NOK
HIGH RISK
STATUS
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.
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.
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 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)
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:
