How do I make rows into column headers and values

Machan1121

Hello,

how can I turn this:

trace #

Acc #

Notes

Syb-Notes

action

action

emp name

Start Date

l1 Manager

End Date

l2 manager

expiry date

l3 Manager

Comments

Memos

88855

12345

Ba Ba Black sheep 123

Marry has a little lamb

Delete

Delete

SDASDA, DAFAGSF H

3/10/2023 8:52

ERTERTRT, SDFSDFSDSDF F

3/10/2023 8:52

RETERTET, SDFSDFF H

3/10/2023 8:52

ASDASDA, VBCVBCVB G

13064

77637

There was a farmer who had a duck

Old McDonald had a farm

Deploy

Deploy

OWOGOWOG, MARK CHRISTIAN C

3/13/2023 7:51

OWOGOWOG, MARK CHRISTIAN C

3/13/2023 7:51

OWOGOWOG, MARK CHRISTIAN C

3/13/2023 7:51

OWOGOWOG, MARK CHRISTIAN C

sdkajsdhs asd asda sda sda

sdasdad asda sda sdas dasd a 123123 123

12987

56518

London bridge is falling down

All the kings horses and all the kings men

Modif

Modif

ASDLAKJA, ALSKDJ

3/10/2023 8:52

LKJLKJNN, ASDA A

3/10/2023 8:52

POIPOI, LIPOIPOI

3/9/2023 11:07

ESOSMF, NNJUDV S

in to this:

trace #

Acc #

Notes

Syb-Notes

action

action

emp name

Start Date

l1 Manager

End Date

l2 manager

expiry date

l3 Manager

Comments

Memos

88855

12345

Ba Ba Black sheep 123

Marry has a little lamb

Delete

Delete

SDASDA, DAFAGSF H

3/10/2023 8:52

ERTERTRT, SDFSDFSDSDF F

3/10/2023 8:52

RETERTET, SDFSDFF H

3/10/2023 8:52

ASDASDA, VBCVBCVB G

13064

77637

There was a farmer who had a duck

Old McDonald had a farm

Deploy

Deploy

OWOGOWOG, MARK CHRISTIAN C

3/13/2023 7:51

OWOGOWOG, MARK CHRISTIAN C

3/13/2023 7:51

OWOGOWOG, MARK CHRISTIAN C

3/13/2023 7:51

OWOGOWOG, MARK CHRISTIAN C

sdkajsdhs asd asda sda sda

sdasdad asda sda sdas dasd a 123123 123

12987

56518

London bridge is falling down

All the kings horses and all the kings men

Modif

Modif

ASDLAKJA, ALSKDJ

3/10/2023 8:52

LKJLKJNN, ASDA A

3/10/2023 8:52

POIPOI, LIPOIPOI

3/9/2023 11:07

ESOSMF, NNJUDV S

CharlieS

Hi @Machan1121 

The key tool here is the Cross Tab, but we need to do some preparation before that action takes place. 

First off, we need to correct an error in the input, There needs to be 2 empty rows here in the data to represent the empty fields in the table. This is important because the transformation is position-based. If the input data cannot be corrected, you would need to explore dynamically identifying the "trace#' values to start a new set of records which could be an unreliable process depending on how unique those values are. 

Once that is corrected, I perform some record/row/field position assignments based on the number of fields you have in the input stream. In this case, that's 15. I set this in a field then use a couple formulas to perform the rest of the assignments for the CrossTab tool. Then I remove the Row field and use a Dynamic Rename tool to update the field names.

Check out the attached workflow to see this in action and let us know what you think.

EDIT: You'll notice that Alteryx requires field names to be unique, so you'll see 'action' and 'action2'. This can be circumvented using the Reporting tools to format a table output, but while the data is in Alteryx, this will be the case.

20230331-CrossTab.yxmd