Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!

How To: Use a Cross Tab without having the data rearranged?


Have you ever wanted to do a Cross Tab, but needed the results in a particular order?  You've probably discovered that the Cross Tab tool will dutifully change your data to column headings and keep the rest of the data correlated, but that the output columns will be arranged in alphabetical order.  It's as if there were a Select tool with the "Sort on Field Name" Option thrown into your workflow without you putting it there.  


You can manage this situation by preserving the initial order with a Record ID.  Use the Record ID to determine the order of the data and then a Dynamic Rename to create the column names.  Here's an example that should get you thinking in a productive direction.


CrossTab preserving order.png


If you add a complication to this by grouping data in your Cross Tab, you'll have to rearrange things somewhat.  In that case, simply stacking the data isn't sufficient because there are multiple rows, only some of which have the column names in them.  One way to handle that situation is to configure the Union tool first using just the column headings, explicitly ordering the Union inputs.  Then adding in the rest of the data once the column order is set.  You can configure the Union to assemble the columns by name, but you have to match the name exactly.  Cross Tab substitutes underscores for punctuation characters, so I added a Formula to mimic that in the example below.


Ordered Cross Tab with grouping.png

This is only one way to do it.  Do you have a better one?  Add it to the Comments!


Alternatively, if you want to group by the names so that they stay in a Name column when you Cross Tab, the workflow can be quite simple.  Take a look at this approach, workflow linked above. (Thank you to @MichelSza for providing this.):




5 - Atom

How about if we want to change the order completely, before writing in an excel sheet?




Generally, you can do that with a Select tool.