# Dynamic Column generation + Column Manipulation

Dear All!
I have the following Problem:
My Table has amoung others 4 Colums: AmountA, TypeA, AmountB, TypeB
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

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

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)

2 Likes