5 Tools & 5 Awesome Features

[ramesh_neel]

On the back of my blog post on 10 tips and tricks (which can be found here). I thought it was time to talk about some powerful and amazing features of some tools which mostly go unnoticed. It’s time to celebrate them :)

Source: Unsplash

1. Find Replace Tool

To me, Find Replace is one of the most underrated tools in Alteryx, given what it is capable of, and is mostly overshadowed by its big sibling, the Join tool. Given its name - Find Replace, people often tend to look at it as a way to replicate the Excel VLookup functionality of simply finding a certain string in another string and replacing them. But what I love the most about this tool is the ‘Append Field(s) to the Record,’ which works like an outer join. So in scenarios where you would want the non-matching records also to be included in your output, rather than using a Join tool and further a union tool to club both the data sets together, you can use the Find Replace tool with the option ‘Entire Fied’ and ‘Match Whole Word Only’ selected (to replicate an inner join behaviour to find exact matches) and link two data sets together on the common field under ‘Find within Field’ and ‘Find Value,’ and all you have to do next is click on Append Field(s) to Record and select the fields you would like to be part of your final data set.

In the below example, the first data set contains 8 US states, and the second dataset contains US states along with state codes, region, and division. Our aim is to find the state codes, regions, and division against each state from the first data set and produce final data with these fields. Please note that some states do not have a corresponding entry in the second data set, but we would want every record from the first data set.

One way to achieve this is to use a Join tool and link the two datasets and add a union tool further to include the states with no corresponding state code, region, and division. But the easy way would be to use the Find and Replace tool with the append functionality as below!

As you can see, we have not used any Union tool here, but the final output automatically includes States with no state code, region, and division in the final output!

2. Join Multiple Tool

So, you thought the only time anyone would ever use Join Multiple is when there are more than two input data sets? Not really! This tool works perfectly fine with two datasets as well, and the bonus is that, like the Find Replace Tool above, it returns the unmatched records from all your inputs eliminating the need to have a combination of Join + Union from both your inputs.

Let’s look at the below example. I have used a dataset like the one in the Find Replace tool example above, but the difference is that the second data set here has two additional states that are not present in the first dataset. (Nebraska and New Hampshire). Our goal is to link the two datasets on State and State Name and produce a dataset of matching and unmatching records.

Traditionally, we would use a Join tool and club it with a Union tool from all three anchors of the join tool (L, J, and R) to produce a final dataset. But the easiest way to achieve the same results without the use of a Union tool is to use the Join Multiple tool. Yes, that’s right, and here is the output! Super cool! Isn’t it?

3. Cross Tab Tool

The Cross Tab tool needs no introduction. In simple terms, it is used to pivot data from a vertical layout to a horizontal layout. But it also has a feature that I have found incredibly useful in many situations. Let’s look at this data set of region-wise sales figures between 2020 and 2022, which you want to pivot so that years appear as columns and region appears as rows, and sales figures are added up together.

Pivoted:

But what if you want to also add row-wise and column grand totals to the final output? No problem at all! Cross Tab tool has you covered. All you need to do is select ‘Total Column’ and ‘Total Row’ along with ‘Sum’ in this scenario as we want to add up all the sales figures under the ‘Method of Aggregating Values’ section, as shown in the below screenshot, and hit run.

Voila!

(You can add a formula tool and replace the null region with ‘Total’ and rename the headers as well to make it even more meaningful.)

4. Summarize Tool

Did you know that Summarize tool can be applied to a dataset produced by a reporting tool like a Table tool to split your dataset either vertically or horizontally (which cannot be done by a Cross Tab tool)? Let’s look at an example and see how this works.

Below is an output of a Table tool with the Pivot method set as Cross Tab producing month-wise sales split by Gender, but you want to display the data set horizontally instead of vertically. No need to scratch your head if you have the Summarize tool! (Data set downloaded from Kagle.com.)

All we need to do is add a Summarize tool next to the Table tool, connect them together and change the Action to Combine Horizontal to pivot the table output.

And here is the output!

5. Explorer Box

Wait! What? Explorer tool? What is that? That’s the common reaction I get from most people every time I mention this tool. Yes, there is such a tool called an Explorer box which can be used to display URLs or File Directories! Here is an example where I have used this tool in my workflow in Alteryx Designer linking to a local drive on a machine that has Alteryx samples. It behaves exactly like windows explorer, and you can sort any fields and even drag and drop a file from here to your Alteryx designer. Quite handy in a workflow that uses file inputs, and you would want to see what files are sitting in a specific folder without having to navigate that folder frequently.

So these are some of the features that I have found incredibly powerful. I hope you find them useful too. In case you have come across something similar, please feel to share them via the comment section below :)