This week we have been working our way through the Alteryx weekly challenges – they are a great way to learn new tools and techniques and present you with problems you may not face in your day to day work. In short, they are an excellent way to expand your Alteryx knowledge – so do them! I came across this problem whilst working on the week 5 challenge. (The solution is in this blog post so go and try it first if you don’t want to know how it was done!)
So what was the problem?
The task was to design a basic Alteryx app that enables the user to search a data set of Job IDs by selecting a Job ID from a drop down list, to return only the records that contain the user-selected Job ID in any of the fieldss. The solution requires a basic custom filter, where the value on which to filter is chosen by the user with a drop down tool. Any value from all 3 fields of the data could be chosen as the input. So we need to get all possible values from the data set and put them into the drop down menu options. The community solution offered the following way to complete the task.
- First use transpose to put the values for all fields into one column
- Summarise this column by grouping by value and selecting a count (we’ll need this later)
- Filter the nulls
- Copy the values from the field Values from the filter’s True output and paste the values into the drop down tool, using ‘Manually Set Values
Copy and paste into the configuration window…
5. The search value (which can be changed using the drop down and action tool) is appended to the dataset
6. A custom filter using the user defined search value then gives the desired records.
Not a bad solution, but I strongly objected to the copy and paste part! It felt very crude and isn’t a dynamic solution. If the data is updated and new values appear, the app is essentially broken as the drop down options won’t change based on the data without another copy and paste job.
What else can be done?
If we take a look at the drop down options on the drop down tool configuration there are a number of other options, however none seem to allow us to use the values from the data in the workflow. One workaround is to use the ‘Fields from connected tool’.
Simply connecting the True output from the filter to the top of the drop down tool only allows us to use the field names in the drop down list, which again isn’t helpful as we want the values.
There is a way we can trick Alteryx into giving us those values though. Here is that trick:
Putting a Cross Tab tool in just before the drop down tool and selecting Value as the new column headers will make all of the values column headers, which the drop down tool will then populate the drop down list with. Boom.
Now we have a drop down tool that will dynamically change depending on the dataset!
I tidied up the workflow a little, and instead of appending the search term to every record, I set the action to change a specific value in the custom filter formula. Now we have the same result but with fewer tools and a cleaner workflow.
