Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Cross Tab - Column Header Issue

Tofique1
5 - Atom
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”
12 REPLIES 12
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Tofique1
5 - Atom
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
20 - Arcturus
20 - Arcturus

@Tofique1,

 

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

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Nicholas_Brill
6 - Meteoroid

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
9 - Comet

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

rashish123
5 - Atom

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
6 - Meteoroid
@rashlish123
If you look at my solution above it should solve your problem
rashish123
5 - Atom

Thanks a lot @Nicholas_Brill. It works properly.

Sbinkley
7 - Meteor

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

Labels