Horizontal P&L into database

[tryingtosolve]

Hi all:  I have a P&L file with data laid out across and I want to turn it into a database.  One of the things that is throwing me off is that there are rows across the top with jurisdiction data that also need to be in the database...

database should look like:

asia  1  ABC AU sales  100

asia  1  ABC AU cogs   50

Thoughts on how to best accomplish this?  I tried playing with cross tab/transpose but getting stuck.

Thank you.

[jdunkerley79]

I would suggest:

Makes sure the Input has first row contains data selected.

Split the input into header and data using 2 sample tools

For the header rows add a RecordID and then you can transpose and cross tab to give the header for each column

For the data, transpose it then join to the header

This should allow the shape you need

Sample attached

Hopefully enough to get you going

[atcodedog05 · Accepted solution]

Hi @tryingtosolve ,

Here is a workflow for the task.

Input : File given by you.

Output : 

Workflow : 

Hope this helps : )

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

[huynv]

I attached a workflow,

Hope it help

[huynv96]

You can see my workflow attached

[vstefano]

Hi please find attached solution from Italy...

[tryingtosolve]

This solution worked well!   Thanks so much all!

[atcodedog05]

Happy to help 🙂 @tryingtosolve 

Cheers and happy analyzing 😀