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

[Ladarthure · Accepted solution]

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!

[Qiu]

@RomainDv 
I hope I understand your intention correctly.

[RomainDv]

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