Cross Tab - Column Header Issue

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”

Input

Regex

Output

Dynamic Processing

Preparation

Accepted answers

MarqueeCrew

@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

Dynamic Field post Cross Tab.yxmd

All comments

MarqueeCrew

@Tofique1,

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

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

Dynamic Field post Cross Tab.yxmd

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.

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.

Dynamic Field post Cross Tab NB.yxmd

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

Sbinkley

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

jacob_kahn

This is the best response I've seen. Thank you for sharing it with us.

Happy Holidays,

Jacob

MLa

Hi,

thanks for this workaround.

But honestly, isn't this a bug in the tool? I was using the crosstab tool in context of generating a JSON File with a given structure. Replacing certain characters tends to break those.

Is there any bug report or an idea yet that I could upvote?

Or is there a chance for a CReW Macro that does this better?

Regards

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703