Data split - currencies

[Hettie2102]

Hi all, 

I currently have a data set where each row has columns of multiple currencies. I need to split this out so each currency has a separate row but I'm struggling on how to do this in the most efficient way. Please see attached document for requirements. 

Thanks so much in advance! 

[atcodedog05]

Hi @Hettie2102 

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[jdunkerley79 · Accepted solution]

I would suggest:

First, transpose it to be row-based.

You can then use a Text to Columns tool to split currency and field

After that, a select tool can be used to rename columns

Finally, a Cross Tab puts the rows back to the column format

Sample attached.

[Hettie2102]

Thank you! That works perfectly 

Sorry I have another question. How would you then create a CNVUSD row which adds the other balances. 

Please see attached document. 

Thanks 

[atcodedog05]

Hi @Hettie2102 

Dont take me wrong. Just want your feedback did my solution fail anywhere.

Just taking feedback so that i can improve myself 🙂

Thanks, Cheers and Happy Analyzing 😀

[jdunkerley79]

Something like:

First I compute the value of each currency in USD

Then you can join this to the row based data to create the CNVUSD totals

After this you can union this onto the original set to get the extra rows