HI FOLKS,
I have some raw data in the below format
ID NAME year scenario month$ monthhc
1 ZY 2002 expense 100 1
I wish to do the below:
ID NAME 2022EXPENSEMONTH$ 2022EXPENSEmonthHC
1 ZY 100 1
i have more than 40 M rows with multiple scnearios
What is the most ideal way to acheive this?
@SouravKayal can you provide both input and output in an excel format (tabular), the given one is confusing
Without much extra information, it's hard to know how much variation there is in your inputs, which makes it difficult to provide a solution. I've attached a workflow that works for the small sample you've given here, though if the data can change much outside of this then it will need tweaking.
Input:
ID | NAME | YEAR | SCENARIO | MONTH$ | MONTHHC |
1 | XYZ | 2022 | Expense | 100 | 1 |
Output
ID | NAME | 2022 Expense Month$ | 2022 Expense MonthHC |
1 | XYZ | 100 | 1 |
@SouravKayal if it's in table format like your latest reply then you can just use the bottom flow (attached). From your initial post it seemed like it was in a raw format which you would need to parse.
@DataNath , thanks , trying this, it could work, although i am seeing the $ getting truncated, any reasons ?
The '$' is replaced as it's a special character - you'd have to rename it after the cross tab I believe.
EDIT: @SouravKayal I've added a dynamic rename tool which solves this issue after the cross tab. Attached: