Hi there,
I am currently doing one automation for my team, and I reach out to very inconsistent data, and I can’t figure out how to transform further as per my business need. Can someone please help me here.
As per the below Input data we have 3 main buckets as Derivatives, Short and Long. Record ID 1 is the total exposure of Derivatives bucket and below that securities of Derivatives bucket till before Short. Record ID 6 has Short bucket which is main bucket and then we have Long bucket in next record that means we do not have any securities in Short bucket. Record ID 7 is Long main bucket and again below that securities which has Long exposure. From Record ID 1 to 5 has total exposure and in Long and Short bucket we will have same securities as Total level segregating between Long and Short depending on the data.
I want to transform this data in such a way that total exposure will stay as it is ie. From RecordID 1 to 5 (before Long or Short word) and Long and Short exposure should populate in column wise in the added columns as per shown below output table. In other words vertical short and horizontal Gross value which us 11 should populate in the newly created Short column and same thing for long.
Input data:-
| RecordID | Sector | Gross | Net |
| 1 | Derivatives | 19 | -11 |
| 2 | FX-A | 3 | 1 |
| 3 | FX-B | 1 | -1 |
| 4 | FX-C | 0 | 0 |
| 5 | FX-D | 0 | 0 |
| 6 | Short | 11 | -11 |
| 7 | Long | 7 | 0 |
| 8 | FX-A | 3 | 1 |
| 9 | FX-B | 1 | -1 |
| 10 | FX-C | 0 | 0 |
| 11 | FX-D | 0 | 0 |
Output table:-
| RecordID | Sector | Gross | Net | Long | Short |
| 1 | Derivatives | 19 | -11 | 7 | 11 |
| 2 | FX-A | 3 | 1 | 3 | 0 |
| 3 | FX-B | 1 | -1 | 1 | 0 |
| 4 | FX-C | 0 | 0 | 0 | 0 |
| 5 | FX-D | 0 | 0 | 0 | 0 |
