Hi masters,
I came across the situation below where I have a table that have 3 columns for every month of the year
TC | COF | EPM | TC | COF | EPM | TC | COF | EPM | |||
Account | Type | Product | Jan-23 | Jan-23 | Jan-23 | Feb-23 | Feb-23 | Feb-23 | Mar-23 | Mar-23 | Mar-23 |
0001 | Revenue | AAA | 50 | 20 | 15 | 45 | 35 | 20 | 25 | 35 | 45 |
0002 | Revenue | AAA | - | 30 | 8 | 38 | 28 | 13 | 18 | - | 20 |
0003 | Expense | BBB | 15 | 23 | 8 | 38 | 28 | 13 | 18 | - | |
0004 | Revenue | DDD | 35 | 20 | 20 | 35 | 45 | 50 | 20 | 15 | 45 |
0005 | Revenue | AAA | 20 | - | - | 15 | 38 | 8 | 38 | - | - |
0006 | Revenue | CCC | 50 | 20 | 15 | 45 | 35 | 20 | 25 | 35 | 45 |
0007 | Expense | BBB | 43 | 13 | 8 | 38 | 43 | 13 | 8 | 38 | 28 |
0008 | Expense | BBB | 45 | 50 | 20 | 15 | 45 | - | 15 | 45 | 35 |
I would like to group the columns with the same month, having only the total per month (see below). Is there an easy or right way to do it (like a dynamic columns grouping) considering that the original table has thousands of rows and 36 columns of month (3 x 12 months).
Account | Type | Product | Jan-23 | Feb-23 | Mar-23 |
0001 | Revenue | AAA | 85 | 100 | 105 |
0002 | Revenue | AAA | 38 | 79 | 38 |
0003 | Expense | BBB | 46 | 79 | 18 |
0004 | Revenue | DDD | 75 | 130 | 80 |
0005 | Revenue | AAA | 20 | 61 | 38 |
0006 | Revenue | CCC | 85 | 100 | 105 |
0007 | Expense | BBB | 64 | 94 | 74 |
0008 | Expense | BBB | 115 | 60 | 95 |
Thanks
Felipe Oliveira
Solved! Go to Solution.
Hi @Felipe_Amancio,
Utilizing Transpose and Cross Tab tools will enable you to group these headers in a dynamic and easy way.
First remove the top first header either in the input tool configuration or through a dynamic rename tool. From there Transpose the data by grouping the main key columns (Account, Type, Product) and setting the dates as the data columns along with Dynamic or Unknown Columns then remove the trailing numbers generated from the duplicate header warnings. After that's done you can just simply Cross Tab the data back into the format you are looking for. Adding in some extra steps in between can ensure the columns are sorted in the right way.
See attached for a completed workflow example.
Thanks StevenP! it worked great!