Alteryx Tools Part 2: Filter, Sample and Date Time
Alteryx has a vast number of tools, and it’s easy to miss some functionality that might be useful, so for this new series of blog posts we’re going to take readers through three tools per blog post, detailing functionality as well as hints and tips for each tool. This is part two, you can find part one here.
The Filter Tool
The Filter tool queries records from input to meet specified criteria. Two outputs are created – True and False. Criteria is set in the configuration box by creating an Expression.
The tool is extremely useful in the data preparation stage. I’m going to use the National Student Survey data as an example. This data set contains survey results for all Higher Education institutions. But what if I wanted data just for my particular institution? Then we can use the filter tool to do just that.
Select your data file using the input tool, then connect up the filter tool. I used the Expression [Institution] == “University of Manchester”. In the True output I get all survey results for that Institution, and in the False output I get everything that does not match the expression.
The Sample & Random % Sample Tools
The Sample tool selects only a specified portion of the records from the input. The choices are quite broad ranging too: from the First or Last N Records, to selecting a truly random set of records.
The Random % Sample tool allows you to select a random number of records, or a random percentage of records.
Some great use cases for this when carrying out surveys. You may have a dataset of hundreds of thousands (or more) contact details. But you want to create a random sampling. Using the Random % Sample option in the configuration you select what random number or percentage of records to select. The output could then be your contact list.
Date Time Tool
The Date Time tool will standardise date/time data for use in other tools downstream, or it’s a great way of formatting your dates before you plug your data in to Tableau.
The tool works both ways – you can convert a date/time field to a string, or you can convert a string to a date/time. Using the list of date/time formats in the help files you can convert pretty much any format.
In the example I have connected some data I have exported from Runkeeper. However, the time field is text and annoyingly has characters in it (e.g. 2013-08-17T08:30:13Z). I just want to pull the date out of this field. Connect the input to the Date Time tool and select whether you are converting from Date/Time to String or vice versa. You then type in the field name you wish to format, and the format that the field is in. The output includes a new field with lovely parsed dates in.
The alternative to the Date Time tool is to use a Formula tool with date/time calculation. I have also included this in the attached workflow. This can be a bit tricker, but doesn’t limit you to the formats set in the Date Time tool.
Download the Alteryx module using all these tools.
So, how would you handle a date string that came in with this kind of format?
9/2/2014 3:11:15 PM
Hi Jeff,
This one is a bit trickier, you wouldn’t be able to use the DateTimeParse tool as it is in a format which this tool doesn’t cover. You would use the Formula tool and the DateTimeParse() function. I have put together a module based on this date format which you can download here. Hope this helps, let us know how you get on.