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!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
DavidP
17 - Castor
17 - Castor

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
joshuaburkhow
ACE Emeritus
ACE Emeritus

Absolutely brilliant article @DavidP ! 

Inactive User
Not applicable

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!

MikeLR
8 - Asteroid

brilliant, thanks!

CassC
Alteryx Alumni (Retired)

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

 

andreap
5 - Atom

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

DavidP
17 - Castor
17 - Castor

That's a very neat trick with the Summarize tool @CassC - 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

 

CassC
Alteryx Alumni (Retired)

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.

ChrisTX
15 - Aurora

Here's a nice solution from @AdamR_AYX 

thread:  https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-handle-special-characters-when-...

module here:  http://downloads.chaosreignswithin.com/community/KeepFieldNamesThroughCrossTab.yxmd

 

 

And from @BenMoss 

I've now created a new tool and made it available on the public gallery. The 'Cross Tab Real' is designed to negate this issue.

thread: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-handle-special-characters-when-...
new Gallery link: https://gallery.alteryx.com/#!app/Cross-Tab-Real/5aca8f788a933718049b6a77

 

The Cross Tab Real tool contains the same functionality as the Cross Tab tool that is built in the Alteryx Designer product; however there is a known issue with the Cross Tab tool that means that if your new headers contain special characters these characters will be replaced with underscores. This macro ensures headers retain their integrity.

An example workflow containing this macro can be found here: http://bit.ly/2F5otgP

Documentation on how to use the macro can be found here: http://bit.ly/2GUecEw

 

jroot
8 - Asteroid

It would have taken me hours upon hours, if ever, to come up with a solution for this. Brilliant!

mceleavey
17 - Castor
17 - Castor

@DavidP , you were absolutely not the only eejit who didn't know this.

 

Very useful!

marcusblackhill
12 - Quasar
12 - Quasar

Please, someone give an Oscar to @DavidP ! Thank you for sharing, no more boring workarounds for that.

 

marcusblackhill_0-1657895706713.gif