Alteryx Tools in Focus: Dynamic Replace and Dynamic Rename

Introduction

Here at The Information Lab we keep finding scenarios where Alteryx really makes our Tableau experiences a whole lot easier. This blog post is going to introduce the power and simplicity of the Dynamic Replace and Dynamic Rename tools in Alteryx. I don't often explore the 'Developer' set of tools but was pleasantly surprised at how easily they allowed me to deal with a data preparation problem I was facing.  I've prepared a simple case study to talk you through how I used them. Hopefully it will set you up for similar success if you're already using Alteryx, and if you're not, it might just give you a reason to take a closer look at the product (our FREE Alteryx for Tableau Users training sessions are a great place to start - link).

Background

So let me set the scene. I've got some data I want to prep for use in Tableau. Its what I'd consider a typical table of data with dimensions and measures, but on closer inspection the individual attributes have been stored elsewhere, together with the actual dimension names. Perhaps that's more easily processed by taking a look at such a data table, so here it is:

Figure 1

ID	ColA	ColB	ColC	Measure
1	GB	1	3	1000
2	GB	3	3	234
3	FR	2	3	34
4	GB	2	2	33
5	FR	1	2	33
6	I	3	1	44
7	I	1	2	80
8	I	2	1	256
9	BR	1	1	3009
10	FR	2	2	33

With the attributes of ColA, ColB and ColC stored separately, as part of a master list of possible values:

Figure 2

Field	Value	Label
ColA	UK	United Kingdom
ColA	FR	France
ColA	D	Germany
ColA	I	Italy
ColB	1	Tableau Desktop
ColB	2	Tableau Online
ColB	3	Tableau Reader
ColC	1	Data Analyst
ColC	2	BI Analyst
ColC	3	DBA
ColC	4	IT Manager
ColD	1	Fundamentals
ColD	2	Advanced

In a similar manner, the dimension names are here:

Figure 3

Column	Label
ColA	Country
ColB	Product
ColC	Role

What to Do?

'That's interesting' was my first thought!Initially I thought a solution would be a basic JOIN tool to resolve out each column in turn. So that was quickly implemented for one column, but looking across what in reality was 10+ columns you could see it was going to get a little tedious and the end result would not be very generic.I did ponder for a moment if a SQL based solution might work, again, repeating a number of joins for each column, but I'd end up with a query specific to just this arrangement of columns. I wanted something a little more dynamic. Time to explore the Developer toolset where I'd heard a better solution might be found.....

Dynamic Replace

So the Dynamic Replace tool is the answer. Lets a look at this tool in a little more detail. We've got 2 inputs, one is Data and one is the Replace logic. My aim with this tool is to replace all values in one pass. In short the logic defined within the R input will be applied across all data in the D input.One subtlety to note is that I've got a mix of data types within my data, so I'm going to ensure all have been converted to strings before I hit this tool.So what do I need to do to prepare the replace logic? Its pretty simple, I just need to define an expression, and the expression will be based upon the _CurrentField_ variable. From the below you can see why I aligned to strings values on my Data input.

Figure 4

The eagle eyed amongst you might notice a subtle difference in Figure 4 compared to Figure 2 above. I've actually applied a filter to remove labels for ColD. The replace tool will error if you supply expressions for a column not in the Data input.So onto the configuration of the Dynamic Replace tool. Its pretty simple, just 3 key options to set, all referencing what you can see in Figure 4

1. What is the name of the column which identifies the fields in my Data input - that is the Field column
2. Where is the expression - that's the Expression column
3. What do I want to output when the expression is evaluated and returns TRUE - that's the Label column

Figure 5

When I look at the Output of the tool, I get what I'm after:

Figure 6

The Summary output of the tool just gives you an overview of how the Replace logic was applied.So to recap I've used the Dynamic Replace tool to perform a 'like for like' replace, a specific value has been replaced with another. However my expression could have been any formula, where a replacement is applied when it evaluates to TRUE. I could have tested a numeric field against a range for instance.Back to my example, where we just have the column headers to tidy up....

Dynamic Rename

The tool we need for this task is Dynamic Rename. In this case we've got a Left and an optional Right input. There are a variety of configuration options here. If you just have a single Left input you can use formulas to edit the column headers or amend specific prefix/suffixes etc. If you include a Right input, then you can use values from this input to rename the headers in the Left.

Figure 7

I'm going to choose the highlighted option above. Into the Left I'm going to route the output from my Dynamic Replace, and into the Right I'm going to route the column headers which I'll reproduce here:

Figure 8

The final piece of configuration is to identify which is the old value - Column, and which is the new value - Label.

Figure 9

With that, we just need to play the module and we have our final output:

Figure 10

The elegance of this workflow is that its generic, we can re-use it or we could even turn it into a macro which accepts the location of the 3 input files. For me, that's a lot of data preparation time saved.I've packaged up a zip of the workflow and the sample data for download from here. The Data Input tools all reference the included Excel file, which I'd located in c:dynamic.