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 Knowledge Base

Definitive answers from Designer Desktop experts.

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

lepome
Alteryx Alumni (Retired)
Created

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 toolwill 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 thena 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
lepome
Alteryx Alumni (Retired)

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

 

siddharthdubey
5 - Atom

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

lepome
Alteryx Alumni (Retired)

@siddharthdubey 

 

Generally, you can do that with a Select tool.