Special characters and the Cross Tab tool.

Lets say you have data that looks something like this…

2017-02-06_18-47-04

And you want to change it to read something like this…

2017-02-06_18-47-31

Well with Alteryx that’s pretty simple, we just drag a cross-tab tool onto the canvas and configure it as appropriate and you have the correct result.

2017-02-06_18-48-59.png

But what you may not have tried before is to create your headers from a field that contains dimension members that have special characters. Lets take our original dataset and replace spaces with a hyphen (-).

2017-02-06_18-44-41

2017-02-06_18-45-24

ARR, we have a problem, you will see that the hyphens have been replaced with underscores (_). I don’t know the exact reason why the tool has been built like this, but i’d be interested in finding out. But in the meantime here’s my way around it.

The Cross Tab Real tool.

2017-02-06_18-58-00.png

This tool has been designed to provide the same functionality as the Cross Tab tool within Alteryx, but maintains the integrity of special characters within headers.


Here’s how it works.

  1. Create an alias for each of the dimension members within our dataset. This alias will not have special characters within it. To do this I first sort my data by the field I wish to make my headers from, and then use the multi-row tool to create a uniqueID for each member.

    2017-02-06_19-03-22

  2. Then we will cross-tab our data using the Alias as the header field.

    2017-02-06_19-07-56

    2017-02-06_19-08-42

  3. Perform a 2nd cross tab from our multi-row tool. This time we will configure it again with our alias field as the header field, but this time instead of choosing our true value field as the values in our output we will use the our actual header field, in my case, ship mode.

    2017-02-06_19-33-24

  4. Take a sample row from this 2nd cross tab stream. This will leave is with one row with our actual header fields underneath the alias names.

    2017-02-06_19-32-20

  5. I will now union this header stream on top of the stream creating in point 2.

    2017-02-06_19-32-32

  6. Now it’s just a simple case of using the dynamic rename on our alias fields and using the ‘Take Field Names from First Row of Data’ option and voila, we now have our cross tabbed data with the true headers

2017-02-06_19-30-28


Now I am definitely not the first person to solve this person, Alteryx egineer Adam Riley has posted a solution on the Alteryx community, and there is also a 2nd solution, again on the Alteryx community, which can be found here.

If you wish to view the workflow used in this post then it can be found here. And remember to try out the Cross Tab Real tool which packages this workflow up into a single tool for easy use.

Ben

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s