community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

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

Alteryx
Alteryx
Created on

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!

Attachments
Comments
Alteryx
Alteryx

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.):

 

MichelSzaCrossTabSolution.png

 

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

Alteryx
Alteryx

@siddharthdubey 

 

Generally, you can do that with a Select tool.