Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
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
Becomes this
... 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!
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:
And the result is:
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
