adding values from two columns where the column names are unknown in advanced

Dear Community,
My Table has the following structure:
id | ValueA | NameA | ValueB | NameB |
1 | 22 | X | 0 | ? |
2 | 23 | Y | 11 | Y |
3 | 155 | Z | 25 | X |

Now I would like to have a table where each name in NameA and NameB has it’s own column:
id | X | Y | Z |
1 | 22 | ? | ? |
2 | ? | 34 | ? |
3 | 25 | ? | 155 |

Remark: I Do not know the content of col NameA and col NameB in advance.

I tried to pivot over both Name fields and to join the table, but this leads to douplicated columns

A Pivot over NameA with Group = id and a sum over ValueA would give me:
id | X | Y | Z |
1 | 22 | 0 | 0 |
2 | 0 | 23 | 0 |
3 | 0 | 0 | 155 |

A Pivot over NameB with Group = id and a sum over ValueB would give me:
id | X | Y |
1 | 0 | 0 |
2 | 0 | 11 |
3 | 25 | 0 |

BUT the final join would douplicate column X and Y:
id | X | Y | Z | X (right) | Y (right) |
1 | 22 | 0 | 0 | 0 | 0 |
2 | 0 | 23 | 0 | 0 | 11 |
3 | 0 | 0 | 155 | 25 | 0 |

A math node or a scripting node are not helpful, since I do not know the values in NameA and NameB.
Is there a solution to this problem?
Thanks
Matthias

Hi @Matthias_W

I think this wf can help you out? splt_and_combine.knwf (31.5 KB)
image
take a look,
gr. Hans

3 Likes

I found the solutions myself: After Pivoting, I used the concatenate node and a group-by node
:slight_smile: Matthias

2 Likes

Thanks Hans, your solution is var more elegant as mine. I pivoted 2x (NameA, NameB) frist and then concatenated the outcome and hat to group (Sum over id) it again. This is also more time-consuming than your solution.

2 Likes

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