This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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!
I hope this is simple to solve, unfortunately I’m just not getting it.
So I have Columns A,B,C and D. I want to use cross tabs tool on Column C and D, where C will be the new Column headers and D will be the values. Everything works fine except that my new Column headers were slightly renamed.
Before the Cross Tabs, Column C was a list of Taxonomies, example of one of many taxonomies- “T.123.45.6” and after the cross tabs tool the Column names were something like this - “T 123 45 6” ... all the “periods” were replaced by a space or underscore. How can I get my Column headers back to having a period? In excel I would just do, replace character “space” with “period”. Why does crosstab tool remove the period?
The reason I need the Column headers exactly how it was before the crosstab, is because I plan to Union this into a template where every column on that template is a taxonomy. Ex “T.123.45.6”
The cross tab tool converts non-alphanumeric values into Underscores ( _ ). Why? I'm not sure. What to do, it depends upon how well you know your data. If the only values that you'll see include those pesky dots, then you can use a Dynamic Rename tool (select all applicable fields including "unknown") with a formula of:
I've included a workflow for you that goes a step beyond this. It looks at all of the unique "C" column values and then strips the punctuation and replaces them with underscores, just like the Cross Tab tool will do. This data is then fed as metadata to the Dynamic Rename tool. The tool is configured to replace the before and after values so that you get back to the header that you're looking for. This won't however work when T_123 and T.123 values exist. Then the work-around will fall apart because it won't know which is the "right" replacement and you'll just get the 1st occurrence.
I have confidence that the workflow will help you. No picture is included because I'm conserving ink (or I'm just lazy right now).
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
I used a similar option to @MarqueeCrew, but assigned a unique record ID to each column header rather than replace punctuation. This will allow for the use case where you have columns like T.123 and T_123.