Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How do I make rows into column headers and values

Machan1121
5 - Atom

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 #NotesSyb-Notesactionactionemp nameStart Datel1 ManagerEnd Datel2 managerexpiry datel3 ManagerCommentsMemos
8885512345Ba Ba Black sheep 123Marry has a little lambDeleteDeleteSDASDA, DAFAGSF H3/10/2023 8:52ERTERTRT, SDFSDFSDSDF F3/10/2023 8:52RETERTET, SDFSDFF H3/10/2023 8:52ASDASDA, VBCVBCVB G  
1306477637There was a farmer who had a duckOld McDonald had a farmDeployDeployOWOGOWOG, MARK CHRISTIAN C3/13/2023 7:51OWOGOWOG, MARK CHRISTIAN C3/13/2023 7:51OWOGOWOG, MARK CHRISTIAN C3/13/2023 7:51OWOGOWOG, MARK CHRISTIAN Csdkajsdhs asd asda sda sda  sdasdad asda sda sdas dasd a 123123 123 
1298756518London bridge is falling downAll the kings horses and all the kings menModifModifASDLAKJA, ALSKDJ3/10/2023 8:52LKJLKJNN, ASDA A3/10/2023 8:52POIPOI, LIPOIPOI3/9/2023 11:07ESOSMF, NNJUDV S   
7 REPLIES 7
binuacs
20 - Arcturus

@Machan1121 One way of doing this with the Tile Tool

 

binuacs_0-1680295794322.png

 

 

CharlieS
17 - Castor
17 - Castor

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. 

20230331-CrossTab1.JPG

 

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.

 

20230331-CrossTab2.JPG

 

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.

 

Machan1121
5 - Atom

Thank you.

Machan1121
5 - Atom

Thank you 

Machan1121
5 - Atom

I was hoping to get a more dynamic way of doing it, since i'll be working on a bigger number of records and some has notes\memo in the end, some just left it blank. But i guess i have to work on fixing the raw data instead. Thanks.

ArtApa
Alteryx
Alteryx

Hi @Machan1121 - You can create a more dynamic workflow if you can define a pattern. For example:

 

ArtApa_0-1680325934459.png

 

ahsanaali
11 - Bolide

@Machan1121 assuming the trace# and Acc# are integers the attached solution may work for you.

 

ahsanaali_0-1680337787647.png

 

Labels