Horizontal P&L into database

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.

test.xlsx

Workflow

Accepted answers

atcodedog05

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😀👍

Shape.yxmd

test.xlsx

All comments

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

LoadToDB.yxmd

atcodedog05

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😀👍

Shape.yxmd

test.xlsx

huynv

I attached a workflow,

Hope it help

solved.yxmd

huynv96

You can see my workflow attached

solved.yxmd

vstefano

Hi please find attached solution from Italy...

Solution_vstefano.yxmd

tryingtosolve

This solution worked well! Thanks so much all!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703