Tool Spotlight: Dynamic Tools

[lriley]

I wanted to write about a couple of experiences I’ve had recently where I’ve been trying to solve a problem and whilst thinking about elaborate ways in which I could use a huge number of tools to get to a solution I’ve realized there’s actually a tool with an option just for my kind of situation. I’ve been using Alteryx for a few years now and still I find myself going ‘wow, I didn’t realize I could do that.’ This is one of the reasons I love using the product, there always seems to be something new to learn and ways that you can improve how you’ve been using it.

Firstly I want to talk about the Dynamic Select tool, which can be found in the Developer area of the tool box. There are 2 options available from the drop down list; Select Field Types and Select via a Formula. Thinking back, I’ve used the Select Field Types option a few times and in certain situations it can be really useful. But the other day I had a problem where I wanted to select only the first 10 fields in my data, the field names would not always be the same so I couldn’t just select them with a regular Select tool. To be honest I wasn’t really sure of the best way to deal with this, I was considering what I could do by transposing the data and adding a record id but I thought there must be an easier solution… and there was, the Dynamic Select tool.

By using the ‘Select via a Formula’ option you can choose various meta data based criteria to select the fields from, and one of the options available is FieldNumber. So I could just use an Expression of [FieldNumber] < 11 and the job is done, problem solved. So although you may not need this tool very often it could be worth just checking out what it’s capable of and perhaps it can save you some leg work in the future. It is also essentially just like a Formula tool so just imagine what you can do with those Expressions, the formula world is quite literally your oyster!

The next tool I realized something new about, and actually this was within the same Analytic App that I used the Dynamic Select in, is the Dynamic Replace tool, also found in the Developer area of the tool box.

My problem was that I had some demographic variables which I wanted to display in a report using a table, the field names would be the header row for the table. For display purposes I wanted to use the descriptive names which come from the Allocate Metainfo tool rather than the code names which come from the Allocate Input tool or in my situation the Allocate Append tool. I know that I could change the display name of the fields by just manually typing in the descriptive name into the Rename Field text box of the Table tool but the variables in the table would not always be the same so I needed a method that would work for any variable selected from Allocate.

Basically I had my data with the variable codes as the field names and I needed to change them to be the descriptive names.

The Dynamic Rename seemed an obvious choice but to be honest I hadn’t used the tool much before so I was unsure of the best/easiest option to use within the tool. So I started looking at the tool and trying stuff out and then I realized that using the ‘Take Field Names from Right Input Rows’ would fairly easily do what I needed. Awesome!

All I needed to do was to create a lookup table for the right hand input and then it would use that to rename the fields on the left, plus there is an option to ‘Ignore if number of Field Names do not match’ which is perfect because I also had some other fields on the left hand side which I didn’t need/want to rename. The only issue then was to create the lookup table, which is pretty easy to do with the Allocate Metainfo tool and a few other useful Alteryx tools.

The only other thing I had to keep in mind is that if the lookup on the right hand side contained rows for fields which were not in the data on the left hand side it would error, but by doing a transpose of my data and then joining to the lookup table I could make sure I only had rows for fields I actually had.

I think really what I’ve learnt is that although there will obviously be tools which you use more often than others, sometimes it might just be worthwhile taking a look at some of the lesser used tools and perhaps they will surprise you. You never know they might actually solve that problem you’ve been struggling with.