Cross Tab Tool - Removes spaces and special characters and adds underscores

[jalizmaldonado]

Hi, the cross tab tool is changing my string value's special characters with underscores. Are there any recommendations on how to proactively avoid this issue? 

Here is how the data reads before the cross tab tool.

Here is how the data reads after the cross tab tool.

Here is my entire workflow. 

 

[TomWelgemoed]

Hi,

I'm not sure you can change that, but why don't you add a dynamic rename after the cross-tab to rename it to the name you want?

Here I've just first renamed the column using a number in the cross-tab and then I use that number back to the original source to relabel it to the text that contains the special characters.

Hopefully that helps? 

[Qiu]

@jalizmaldonado 

As @TomWelgemoed  pointed out, the cross tab tool would change the field name.

I usually do a RegEx_replace with Dynamic Rename

[echuong1 · Accepted solution]

Using the cross-tab will essentially remove all non-word characters.

I think the easiest and most dynamic way to solve this is to use the Dynamic rename tool. The cross-tab essentially replicates the following regex:

Regex_Replace([Field1],"[^a-zA-Z0-9]",'_') 

Given that, I like to create an "old" and "new" field name reference list. Once I have that, I can use the dynamic rename after the cross-tab to revert the names back to their original form.

[Jan_C_Ott]

Dynamic rename doesn't help in the situation I googled and found this forum for. If you have headers like Permanent - City/Town and Name
Mailing - Address Line 1 it replaces it with Permanent___City_Town and Name_Mailing___Address_Line_1. You can't dynamically rename that correctly especially if you need to union it together with another flow stream that didn't get cross tabbed. Especially if the client is stubborn about there original header names.

[JackTomlinson]

Dynamic rename should work using echuong's method, if not I've used the following alternative:

As far as I'm aware Cross Tab outputs column names in Alphabetical order, you can therefore combo a Summarise tool - grouping by name followed by a sort function to sort the Name Alphabetically.

If you use a dynamic rename with "Take Field Names from Right Input Rows", using "positional rename" you can then replace the column headers which have been updated by the Cross Tab Function with the original field names

 

[bkclaw113]

I like your solution @JackTomlinson  but if you have  fields included in the Group By for the Cross Tab, those fields come first in the output. To get around this you can simply include a text input with the group by fields, and union that to your sorted output from the summary tool.