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 to convert horizontal data table to vertical when there are duplicate head values

wzhu717
8 - Asteroid

hello, 

i need help to convert horizontal data to vertical. currently i have the some headers are the same values and when i tried to do a compose and cross tool it group the headers together. so here are the data and i want it to look like this. I need help.

Capture.PNG

 

9 REPLIES 9
danilang
19 - Altair
19 - Altair

Hi there @wzhu717 

 

WF.png

 

The trick here is add a record ID and then use this as a key field in the Transpose and as the Column Headers field in the Cross Tab

 

InputInputOutputOutput

 

Dan

wzhu717
8 - Asteroid

thanks, i tried to that wa1234.PNGy but then it starts to skipping rows.here is what it looks like.

afv2688
16 - Nebula
16 - Nebula

Hi @wzhu717 ,

 

As @danilang said thats a way to do it, but if you want to set the first field as a column name:

Untitled.png

 

You can use this one too :)

 

Cheers

afv2688
16 - Nebula
16 - Nebula

You have on your workflow a sample tool. Maybe that is the reason for rows being skipped

 

cheers

 

danilang
19 - Altair
19 - Altair

Hi @wzhu717 

 

Does your input data have holes in the first column?  Have you tried my workflow on your data?

 

Dan

wzhu717
8 - Asteroid

yes i tried your method. all i want to do is flipping the entire data to vertical so i can do a dynamic select after that base on the Name. the reason i used a sample tool its because there are data in the first 4 rows i dont need to be included. 1.PNG2.PNG3.PNG

danilang
19 - Altair
19 - Altair

After looking at your image more closely I see what is happening.  The Crosstab tool sorts the rows alphabetically.  This isn't a problem if you have less than 10 rows but with more than that you have the order F1, F10, F2, F3, etc, since F10 comes before F2 alphabetically.  This new version builds a row number field and uses that to order the rows.  You shouldn't have any skipped rows now.

 

 

Dan

 

 

wzhu717
8 - Asteroid

this is what i am looking for. thank you so much Dan. much appreciated. 

danilang
19 - Altair
19 - Altair

Hi @wzhu717 

 

Glad to help.  Since this is what you're looking for please consider marking this solution as accepted.  This will help people to find a solution to similar problems that they may have in the future

 

Dan

Labels