Need help with pivoting the data horizontally

[ivoiculescu2020]

Hello, team

I need your help to transform the data from an Excel report I have. The attached screenshot 1 shows the data how it is currently in my report.

The screenshot 2 shows how I would like the data to look like.

I tried to use the Cross tab but I am really not sure how to get the data to look like in screenshot 2.

Can you please help me?

Thank you!

Kind regards,

Ioana

[davidskaife]

Hi @ivoiculescu2020 

Here is a solution that works with the data you have provided, its not dynamic though so assumes nothing changes..

You have to split the data into two, get the header into the right layout first, then union in the actual data:

Split the data into two streams using Sample tool, restricting to first 13 rows, and skipping first 14 rows (to ignore the null row). Cross Tab both streams, and for the header stream use the Dynamic Rename tool to take the Headers from the first row of data. Then remove the columns you won't need, and union by position to get the completed output.

[ivoiculescu2020]

@davidskaife 

Hello,

Thank you so much for this solution. it worked!!!

I do have another question though: my input file has 407 rows. Do I need to keep repeating the 2nd stream until I get all the rows in order? Or is there a faster way to get the rest of the rows in order?

Kind regards,

Ioana

[davidskaife]

Hi @ivoiculescu2020 

Edit: removed my original response as realised what you meant - let me work up a second solution to handle multiple records!

What would separate the records, a space?

[davidskaife]

Hi @ivoiculescu2020 

I've updated the workflow example to account for multiple records, where they are separated by a null row:

What it will do is generate specific RecordID's and Record Numbers per set of data, to allow for aligning and grouping correctly,

If you let me know if the separator between records is different i can update!