Dynamic Column generation + Column Manipulation

[Matthias_W]

Dear All!
I have the following Problem:
My Table has amoung others 4 Colums: AmountA, TypeA, AmountB, TypeB
Rows are (with a addiation ID-Column and the header row) e.g.:
ID, AmountA, TypeA, AmountB, TypeB
ID1, 10, “A”, 0, “”
ID2, 5, “A”, 5, “B”
ID3, 2, “A”, 8, “A”
ID4, 20, “C”, 5, “C”
ID5, 30, “C”, 2, “D”

Now I would like to have a dynamic (pivot) table which has a column for every Type and the sum of the amounts in it. For the table above the outcome should be (for further processing in Excel):
ID, AmountA, TypeA, AmountB, TypeB, A, B, C, D
ID1, 10, “A”, 0, “”, 10,0,0,0
ID2, 5, “A”, 5, “B”, 5, 5, 0, 0
ID3, 2, “A”, 8, “A”, 10, 0, 0, 0
ID4, 20, “C”, 5, “C”, 0,0,25,0
ID5, 30, “C”, 2, “D”, 0,0, 30,2

How could I possible do this, when I do not know the Types in Column TypeA and TypeB in advanced?
Thanks a lot!
Matthias

[Matthias_W]

By the way: I tried to do concat 2 Pivot Nods. This works fine for the first column (TypeA) but for the second Pivot I need to know the group column which depend on the values of TypeA

[Thyme]

If you have only 2 pairs of input columns (A, B), then this can be done with a “column group unpivoting”, followed by a Pivot node:

1. use a Column Filter to separate columns A and B from each other
2. rename columns so they share the names
3. concatenate - these 3 steps effectively transform your input into the long format
4. Pivot using ID as groups, type as pivot and sum of amount as aggregation - this takes care of a priori unknown types
5. Join with original table to get all desired columns
6. Missing Value to replace missing integers with 0

Produces the desired output. If you have an unknown number of column pairs, let me know.

dynamic column aggregation.knwf (48.7 KB)

[Matthias_W]

It works perfekt! Thank you for your fast reply!
Matthias