Transform a report with several columns into a flat file

RomainDv

Dear Community,

In the enclosed example "Input", I need to transpose the wide dataset into a narrower dataset.

My issue is that I have several rows to qualify each column, therefore the use of the "transpose" tool is not enough.

In have designed my expected output in the tab "output".

Many thanks for your help

Example.xlsx

Transformation

Accepted answers

Ladarthure

Hi @RomainDv,

I did not do the exact expected output but used a few tricks to make your data ready for use.

When you have this kind of files, the trick is to differenciate what is a header, and what is just true data. On this side, you can then use transpose and cross tab to identify rows and which column corresponds to which header level. To finish the process a join does the trick.

Some tricks to use facing those files :

Use record ID to create a unique ID (easier for transposing)
in the input tool, do not hesitate to tick the first row contains data to have it as a plain data set

hope it helps!

prep_transpose.yxmd

All comments

Ladarthure

Hi @RomainDv,

I did not do the exact expected output but used a few tricks to make your data ready for use.

Some tricks to use facing those files :

Use record ID to create a unique ID (easier for transposing)
in the input tool, do not hesitate to tick the first row contains data to have it as a plain data set

hope it helps!

prep_transpose.yxmd

Qiu

@RomainDv
I hope I understand your intention correctly.

0505-RomainDv.yxmd

RomainDv

Thanks a lot @Ladarthure this perfectly fits my expectation. Have a great day

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703