In my work at TABS Analytics, I often find myself building re-usable alteryx workflows for processing sales data coming from syndicated sources such as Nielsen or IRI. The goal is to build tools that can be applied to data coming from multiple different clients. Though the basic content of the data is the same, the field names and format are often different depending on the client. A basic measure such as sales dollars, could come with many different titles such as (Dollars, Sales Dollars, DOLLARS, Value Sales, etc.) In this example I’m going to walk though how I use the dynamic rename tool to “iron out” these variations up front to avoid errors downstream.
Imagine that we have two different data sources with slightly different field names as shown below.
The goal is to build a workflow that can load both data sources, properly identify the fields, and process without errors. In this case, the first thing we’ll need to do is rename the fields so that they are consistently identified throughout the rest of the workflow. The standard Select tool won’t work since it will throw errors if you try to map two different raw field names to the same output name (i.e. mapping Dollars and Sales Dollars to both be renamed as just Dollars).
The solution is to use the Dynamic rename tool as shown below.
I start by creating a field map which is a simple text input tool defining how I want all the different raw field names renamed.
The next step is to use the Field Information tool to get the raw field names coming from the input data source, and join the output of that onto my field map as seen below.
If I connect up the second input and run this workflow, the result of the join expression is a simple list of the old field names and the corresponding new field names taken from my field map.
The final step is to rename the actual old fields in our data stream with the new field names specified by this join. This is where we use the Dynamic Rename tool available under the developer tool palette. The actual data is connected to the L input of the tool, while the old/new field name list is connected to the R input. The tool is then configured to replace the old field names with the new ones as shown below.
If I run the workflow and view the results in the final browse window, I can see that all my raw field names were renamed accurately based on my field map. The result is the same no matter what input data source I connect.
I now have a resilient workflow that will run without errors regardless of what data source I use. If new field names show up down the road, the dynamic rename tool will thrown an error and I will simply need to add those new field names to my map in order for it to process smoothly once more.
I hope this has been helpful. Let me know in the comments if you have any other questions or ideas.