I have 3 fields Unit_ID, Act_Code, and Trans_Amount.
Please look in to the pivaot table in the attachment. where for each activity code i need to create a separate column for transaction amount.
In the pivote table i have taken Unit_ID, Act_Code, as dimensions and Sum(Trans_Amount) as expression. and dragged Up the field 'Act_Code' so that table transforms each Activity code as separate columns. This functionality i need at script level. I need to calculate the no. of dynamic columns for each Activity code which holds the Transaction Amount for each Unit_ID.
There are 9 Activity Codes including Blank records.So i need to calculate 9 columns at loading script. For the Blank record there should be also a column saying Trans_Amount_BLANK.
for example there are few Activity codes say AA, A1. (Lets take 2).
I need to a separate field like Trans_Amount_A1,Trans_Amount_A2.
this logic will repeat for all activity codes. NOTE: There are no Transaction Amounts for Activity Code C2 , So if there is no amount then we can ignore.
I have also attached the sample file with the data.
