new help in data transformation

[anonymous008_G]

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

[LindonB · Accepted solution]

Hi there,

I've attached a workflow that should help. Basically, it involves using a multi-row formula to fill down under 'Derivatives', 'Long' or 'Short'. Then some data transformation to create the Gross/Net columns and the Long/Short columns separately before joining them together. I also added a key filled for each row under each 'Derivatives' in case there are multiple in your actual dataset. Hope this helps and let me know if you have questions or other complications.

[anonymous008_G]

@LindonB --- thank you so much, this works absolutely fine as needed.