QUestion for transformation

[SouravKayal]

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?

[binu_acs]

@SouravKayal can you provide both input and output in an excel format (tabular), the given one is confusing 

[DataNath]

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.

[SouravKayal]

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

[DataNath]

@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.

[SouravKayal]

@DataNath , thanks , trying this, it could work, although i am seeing the $ getting truncated, any reasons ?

[DataNath]

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: