My input is as follows:
Code: Select all
account# currency category fee amount
1001 USD Investment_Retail 100
1001 CAD Equity 200
1001 CHY Group_Liquidation 300
1002 USD Investment_Retail 400
1002 CHY Group_Liquidation 500
Code: Select all
account# currency Investment_Retail Equity Group_Liquidation
1001 USD 100 0 0
1001 CAD 0 200 0
1001 CHY 0 0 300
1002 USD 400 0 0
1002 CHY 0 0 500
1. There can be 3 values of the category column at max
2. If for any account#-currency combination there is no fee amount for a specific category, then it would be defaulted to 0 (zero) in the corresponding category column in the output.
For example, for account#=1001 and currency=CAD, there is no record in the input with category="Investment_Retail", hence the corresponding output record will have a value of 0 in the column "Investment_Retail".
How can I achieve this?
Please help.
Regards,
Kumarjit.