Can the real [Field Name] please stand up...

[DavidP]

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!

* Meaning of eejit

fix crosstab column names with field info tool.yxmd

[joshuaburkhow]

Absolutely brilliant article @DavidP ! 

[Inactive User]

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]

brilliant, thanks!

[CassC]

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.

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

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

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:

And we get renamed fields!

[andreap]

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

[DavidP]

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.

[CassC]

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

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.

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:

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

[ChrisTX]

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]

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

[mceleavey]

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

Very useful!

[marcusblackhill]

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

[Aaron1234]

Hey I just wanted to let you know that 4 years later, this is still extremely valuable info. Still a problem in the 2023 version. Thanks @DavidP