community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Community v19.9

The latest release includes several enhancements designed to improve your Community experience!

Learn More

How to get original data back after Cross Tab Tool inserts underscores

Alteryx
Alteryx
Created on

 

You may have noticed that the Cross Tab tool will insert underscores in your headers.  There may be times when this is ok, but for those times when it’s not, how do you get the original format of the data back - without the underscores?  Attached is an example that provides a solution.  Feel free to bypass the rest of this posting and go directly to the example.

 

2019-06-18_16-32-29.png

 

 

If you'd like a narrative of what is going on in the example, read on.  The Text Input tool has data that has the following data: 

 

 

2019-06-18_16-31-56.png

 

 

We want to cross tab our data, so fields in Dept Name become our new headers with Cost in Millions as our values.

 

2019-06-18_16-33-21.png

 

 

 

So now our data looks like this: 

2019-06-18_16-33-55.png

 

 

where the headers have been changed to include underscores. Very uncool.  So in a separate workflow stream, we're going to get the original format.

 

Attach a Select tool to the Text Input:

 

And select Program Type and Dept Name.  We don't need the Cost in Millions anymore.  We going to use the Dept Name - which is in the original format - and eventually use this to replace the data with underscores.

 

After the select, our data looks like this. 

 

2019-06-18_16-34-37.png

 

 

However, we only need one group for Dept Name.  You can see it the Dept Name is repeated for each Program Type.  So attach a Unique Tool and check Dept Name in the configuration window.

 

 

2019-06-18_16-35-21.png

 

 

 Now we're left with the following data:

 

 

2019-06-18_16-35-57.png

 

 

This is where it gets interesting.  We need to make a copy of the Dept Name, but we want to make it look exactly like the headers with the underscores in it.  Why?  We'll use this new field to align our data in sequent Union tool.  In the meantime, back to creating the new field with underscores in the data.

 

3crosstab2.jpg

 

 

This REGEX_Replace expression will take the current Dept Name field and replace the colons and spaces with underscores.  In the real world, your actual data may contain other characters that were replaced with underscores by the Cross Tab tool.  You'll need to modify this expression accordingly. In this example, the result of the REGEX_Replace statement looks like this:

 

2019-06-18_16-35-57.png

 

 

where Dept Name2 looks like the headers from the Cross Tab tool. 

 

So rather ironically we're going to cross tab this data.  Connect a Cross Tab tool and in the configuration, we'll group by Program Type, make the new headers Dept Name2 and the values will be Dept Name:

 


2019-06-18_16-37-30.png

 

 

The result looks like this:

2019-06-18_16-38-24.png

 



We're ready to union our data at this point.  Since the headers from both Cross Tab tools look the same, we can select 'Auto Configure by Name'.

 

Make sure the Cross Tab with the original format is attached to the Union Tool first.  

 

 

2019-06-18_16-39-04.png

 

By specifying the output order in the Union Tool configuration, we ensure the original field data is on top (rather than the bottom).

2019-06-18_16-39-57.png

 

 

For the grand finale, we'll add a Dynamic Rename tool and configure the tool to 'Take Field Names from First Row of Data'.  Notice that Program Type is not checked in the configuration. That's because this field is already in the format we want, so we unselect that field.


2019-06-18_16-40-33.png

 

 

 Now we have our data, cross-tabbed, with the original format.  Our headers look the way we want them to!

 

 

2019-06-18_16-41-30.png

 

Attachments