Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
16 - Nebula
16 - Nebula

We all love the CrossTab tool right? Right?

 
Except maybe for that annoying quirk where it changes all non alphanumeric characters in your new field names to underscores. Spaces, special characters, not so special characters, anything that is not a letter or a number just becomes an underscore.
 
This
 
 before crosstab.png 

 

Becomes this

 

after crosstab.png

 

 

... where Part # was selected as the Group By field in the the CrossTab tool.

 
It's been driving me crazy for years and I've come up with all sorts of workarounds. These workarounds normally entailed somehow capturing the original field names before the CrossTab and then using the Dynamic Rename tool to rename the underscore-ridden field names after the CrossTab tool. But this gets a little tricky when, for instance, you group by some fields in the CrossTab tool so that your output field names are not exactly the same (or in the same order) as before. So your efforts to restore the field names become more and more elaborate and less and less transferable to the next time you're faced with this problem.
 
But this week, completely by accident, I stumbled upon something so amazingly simple. The real column names, special characters and spaces and all, are still there!!! You just can't see them!
 
Where? 
 
Well, just drag in a Field info Tool onto your canvas after the CrossTab tool and look at the [Source] field! 
 
crosstab field info.png
 
 
Sure, there's a CrossTab:Header:Name:  in front of it and a :Concat:  or a :Sum: or :Average: at the end (nothing a little regex can't get rid of in a jiffy), but there in all its glory are your non-alphanumeric, spaced out, underscore-less field names, ready to retake their rightful place at the top of your output dataset!
 
So now, armed with this knowledge, I can add some very simple steps after my CrossTab tool, like so:
 

dynamic rename.png

 
 
And the result is:
 
result.png
 
 
My initial thought as I was staring at the simplicity of the solution in front of me was, "Wow, so simple, and yet so amazingly useful! How come I never realised this before?"
 
Of course my second thought quickly following the first was, "Am I the only eejit* who didn't know this! Does everyone know and just decided not to tell me? If so, how can you all be so mean!"
 
If any of you out there are like me and also missed the "Field Info tool solves all your CrossTab column name woes" memo, having to accept underscores as a fact of life and stripping special characters and spaces from your field names, I hope this tip will once again bring you closer to your CrossTab tools.
 
Happy Alteryxing!
 
Comments
Alteryx Partner
Alteryx Partner

Absolutely brilliant article @DavidP ! 

6 - Meteoroid

This couldn't have come at a more perfect time, I've been struggling with this exact same problem for the last week.

 

This worked a treat, thank you!

Alteryx Partner

brilliant, thanks!

Alteryx
Alteryx

This is a great solution, David!

I wanted to share the method I've used for this in the past. One of my favorite things about Alteryx is how many ways you can solve the same problem, so hopefully this helps someone.

My solution uses three tools instead of two, but I was able to avoid a regular expression, which is something I really like to do when I can.

image.png

 

I use a Summarize Tool, grouping by our "Name" column, twice. I leave one as "Name" and set the other to "Value"

 

JesseC_0-1596041718670.png

 

 

Then I used a Cross Tab tool, with default configuration (Name, Value, no group by, concatenate)

 

JesseC_1-1596041789221.png

 

 

A Transpose tool then flips this into a really useful "Name/Value" pair with the "cross tab name" and the original name.

I used a slightly different configuration of the Dynamic Rename tool:

JesseC_2-1596041855794.png

 

And we get renamed fields!

JesseC_3-1596041878938.png

 

5 - Atom

Great solution @DavidP but it's the inclusion of the definition of eejit that makes it 👌

16 - Nebula
16 - Nebula

That's a very neat trick with the Summarize tool @JesseC - I'm definitely going to use that one!

 

The other annoying thing about CrossTab is the re-ordering of your columns. Neither of our solutions address that (Look at where Field 1 has moved to)

 

I normally use a Tile tool and Summarize for this as shown below. For your CrossTab you then use the TileSequenceNum for the column headers and swap them for the real Names afterwards with the Dynamic Rename the same way you suggested.

 

This only works if all (to be) rows have values for all columns though, not like in this example for Text 1, 2 and 3 where only 2 of the 3 have values in each row.

 

 
 

DavidP_0-1596063409855.png

 

Alteryx
Alteryx

Here's how I've solved that in the past, @DavidP .

 

JesseC_0-1596210000123.png

 

There's a few changes to this workflow, so I'll try to highlight them here.

 

I used a RecordID tool to create a field called "FieldNum". Then, I grouped by Name and grabbed the Minimum FieldNum and joined this back to the main dataset on name.

 

JesseC_1-1596210086516.png

 

 

I made some minor changes to the top Cross-Tab tool and the Summarize tool on the bottom - instead of using the Name column for the name, I used FieldNum.


The rest of the workflow is the same.

 

That leaves us with sequential field names that are sorted in order of appearance, and are still automatically replaced at the end:

JesseC_2-1596210149390.png

 

 

JesseC_3-1596210163198.png

 

This should work even if columns are missing values, as long as Name is populated.