Hi there,
I'm currently working on a workflow that needs me to agregate and reorganize data based on non-unique ID.
Here is my input :
ID | AMOUNT | TRANSACTION TYPE |
201907103773 | 40000 | INVCAP |
201907103773 | 0 | HON |
201907103773 | 0 | FEE |
201907103773 | 0 | OTHER |
201907103709 | 20000 | DISTRIB |
201907103709 | 1000 | PROFIT |
201907103709 | 0 | INVCAP |
201907103945 | 3600 | HON |
201907103945 | 0 | INVCAP |
I'm looking to obtain this :
ID | INVCAP | HON | FEE | OTHER | DISTRIB | PROFIT |
201907103773 | 40000 | 0 | 0 | 0 | 0 | 0 |
201907103709 | 0 | 0 | 0 | 0 | 20000 | 1000 |
201907103945 | 0 | 3600 | 0 | 0 | 0 | 0 |
Basically I want to transform the different values of the TRANSACTION_TYPE column (there are 20 in my original input) into columns, and allocate each amount to its proper column based on TRANSACTION TYPE, in order to have a unique ID in the end result.
I have no idea how I can do this. Any help is welcome !
Solved! Go to Solution.
Hi @Jonathan_A
Just to add a little more to @rafalolbert 's correct answer, you'd put the ID as a grouping level, the Transaction Type as the Header columns, and the Amount in the Value (use a sum to add).
Cheers!
Esther
That's exactly what I needed. Thank you very much.
However, If I have more columns in my input similar to the ID, how can I have them coming out of the crosstab without them factoring into the end result ?