Cross Tab - Column Header Issue

[Tofique1]

Hi Guys,

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”

[MarqueeCrew · Accepted solution]

@Tofique1,

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:

replacechar([_CurrentField_],"_",'.')

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).

Cheers,

Mark

[Tofique1]

Thank you so much! The Dynamic Rename tool was enough to help me solve my issue. You’re a life saver!!! I appreciate your time and effort. Thanks again.

[MarqueeCrew]

@Tofique1,

Thanks for your gratitude.  It is nice to feel appreciated.  Life Saver?  Maybe a breath of fresh air?

Cheers,

Mark

[Nicholas_Brill]

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.

[Paul-Evans]

Thank you, @Nicholas_Brill, this is what I needed to handle headers in which spaces, slashes, and dashes were all converted to underscores. 

[rashish123]

Hi MarqueeCrew

I am facing a similar issue .My data before going to cross tab was "A B C:D E:F G" which became  "A_B_C_D_E_F_G" later on.

Thanks in advance if you can help me with it .

Regards ,

Rashish 

[Nicholas_Brill]

@rashlish123
If you look at my solution above it should solve your problem

[rashish123]

Thanks a lot @Nicholas_Brill. It works properly.

[Sbinkley]

@MarqueeCrew  - I'd give you a thousand stars for this one if I could.  You just saved me a ton of work!