Grouping columns by date

Question

Hi masters,

I came across the situation below where I have a table that have 3 columns for every month of the year

TCCOFEPMTCCOFEPMTCCOFEPMAccountTypeProductJan-23Jan-23Jan-23Feb-23Feb-23Feb-23Mar-23Mar-23Mar-230001RevenueAAA              50              20              15              45              35              20              25              35              450002RevenueAAA               -                30                8              38              28              13              18               -                200003ExpenseBBB              15              23                8              38              28              13              18               -   0004RevenueDDD              35              20              20              35              45              50              20              15              450005RevenueAAA              20               -                 -                15              38                8              38               -                 -  0006RevenueCCC              50              20              15              45              35              20              25              35              450007ExpenseBBB              43              13                8              38              43              13                8              38              280008ExpenseBBB              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).

AccountTypeProductJan-23Feb-23Mar-230001RevenueAAA              85           100           1050002RevenueAAA              38              79              380003ExpenseBBB              46              79              180004RevenueDDD              75           130              800005RevenueAAA              20              61              380006RevenueCCC              85           100           1050007ExpenseBBB              64              94              740008ExpenseBBB           115              60              95

Thanks

Felipe Oliveira

StevenP · Accepted Answer

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.