WIXLIB

The Finer Things In AlteryxKen Black 7/2/18Controlling Level Of Detail by Parsing DatesThe Century - tostring(tonumber((left(DateTimeFormat([Event Date],'%Y'),2))) 1) "st"The Decade - left(DateTimeFormat([Event Date],'%Y'),3) "0's"

Converting Unix Timestamps (check your work at https://www.unixtimestamp.com/index.php)

Topic 4: Regex and Date Operations (Multiple weekly examples)From Week 4 of the Weekly ]-\d ).* .*(\u\l\l\s\d ,*\s\d\d ).* .*(\d -\u\l\l -\d\d ).* .*(\d[[:alpha:]][[:alpha:]][[:alpha:]]-\d ).*There are four regex searches here- and the example data that matches the search:1. .*(\d\d-[[:alpha:]][[:alpha:]][[:alpha:]]-\d ).*2. .*(\u\l\l\s\d ,*\s\d\d ).*3. .*(\d -\u\l\l -\d\d ).*4. .*(\d-[[:alpha:]][[:alpha:]][[:alpha:]]-\d ).*- 16-APR-2005- Nov 16, 1900- 9-July-2001- 4-SEP-00Notice that the pipe ( ) is used to delimit the searches and that “.*” is used at the beginning and theend of the searches to be able to find the 4 search patterns anywhere in the search area.Alteryx creates 4 output fields sized at 220 to handle the content of the four searches, when the Parsemethod is used.Example matches of these are:

After some additional work using a formula tool,

And a text to columns parse:&Y

the final dates are assembled using the DateTimeParse function:For Reference, here are the specifiers used for dates/time for Alteryx:

The separators:

And the Date/Time Examples:To find a match for anything:*(.*?)*

Datetime Tool Example 1: Custom format date stringFrom Week 16, a custom formatted string (16-JUN-01) is converted to a date (2001-06-16) using thedatetime tool.The custom setting is shown below as d/-Mon.-yy .When this is used, Field 3 becomes a DateTime Out.

Datetime Tool Example 2: Standard format date stringFrom Week 17, a standard formatted string (April 03, 2013) is converted to a date (2013-04-03) usingthe datetime tool.

Week 21 – More Custom Date WorkIn this example, very sketchy date details are provided and complete month/years are created from theinformation. Here is the initial sketchy data followed by the parsing of month and year.Here is the final date output, showing the clever logic used to rename the months:

Topic 5: Multifield searching and matching (Week 5)The append tool is used to create combinations of an input value and records in a database such thatthe input field can be found in any of the columns of the database. The append operation creates thecombinations needed for this to be possible, and a simple if block does the comparisons.The user input of 3333 is appended to the database records. The following logic identifies the recordswhere 3333 is found.

Topic 6: Length along a Polyline (Week 6)A sequence of airport trips are strung together to find out which sales rep as traveled the most miles.The airport lat/longs are given as centroids so all that is necessary is to produce polylines for each salesrep and use the spatial info tool to calculate the distance traveled by each sales rep.

Topic 7: Parsing JSON Data (Week 7)This is an excellent example in so many ways. The methods used to identify the JSON data elements areinsightful and efficient. There are so many excellent maneuvers in this example that it is one of the bestexercises to date. I have rarely used the sample tool, and it is used in two different ways here. I havenever used the JSON tool, so it was good to learn. Finally, the use of regex and the dynamic rename toolwere both good.

Topic 8: Filtering by date (week 8)Given date data like:Configure a filter to allow date-based filtering

Topic 9: Ranking items where there can be more than 1 at the same rank level, and performing a top Ncalculation (Week 9)I like this example because of the use of the sample tool to identify the top N ranks, and also for the useof the clever technique used to assign the ranks (using a join).

Topic 10: Calculating Time (Days, hours, minutes, seconds)Has an error in the naming of the first formula. This says it is a time difference in minutes but is actuallya difference in seconds. Otherwise, excellent instructional on how to calculate discrete time blocks.For a more efficient solution, see the following formulas

Creating a day bucket from a datetime field/* Create the day for counting records */DateTimeFormat([DURATION START TIMSTM],'%Y-%m-%d')Creating an hour bucketDateTimeFormat([DURATION START TIMSTM],'%H:00:00')

The Minute Bucket With the day:DateTimeFormat([DURATION START TIMSTM],'%Y-%m-%d %H:%M:00')The Minute Bucket:DateTimeFormat([CALL START DT],'%H:%M:00')You can also create an hourly bucket for all days of your data like this:DateTimeFormat([DURATION START TIMSTM],'%Y-%m-%d %H:00:00')With this formulation, you will get 24 records per day times the number of days you have in the file.

Topic 11: Linear Regression ModelingI like this example because it uses the Spearman Correlation tool to identify the top 10 statistics that aremost strongly correlated to winning baseball games (lower part of workflow) than then these terms areused in a linear regression model to estimate how teams will do in the following season. I especially likethe use of the scoring tool to determine the teams which are best positioned to win the following year.It would be an interesting study to take historical data, apply this approach and see how accurate theresults were. I’d like to do the same thing for football.

Topic 12: Identifying Data Fields in Sloppy DataThis is example 20 and I like it a lot because of how regex parsing is used to identify different data typeelements like addresses, phone numbers, etc. The buckets are created to hold these fields and I thinkthe approach is novel and robust. There are many real-work examples that could use this approach.The incoming data looks like this:

Once the cleaning and parsing is complete, a nice output structure is achieved:

Here are the details of how the data fields are identified: (Awesome regex examples)

Continuing with the theme of sloppy data, Week 22 has ATM data in a really ugly format and the dollartransactions need to be extracted. This is another nice regex example. Here is the workflow:Here is the regex for extracting the dollar values of the transactions:Here is the result:

Topic 13: Time Series Forecasting Using An autoregressive integrated moving average (ARIMA) modelI really like this example for a few different reasons. Using Alteryx to make predictions is a very practicalusage of the software. I especially like the forecasting at 95% and 80% high and low.Miscellaneous Notes

Flat files are intended to be used with ASCII characters.Quick Reference For All Toolshttps://help.alteryx.com/10.6/Getting Started/AllTools.htm

Browse ColorsBrowse Metadata

Data Types

Boost Regex

Alteryx Keyboard Shortcuts

Alteryx creates 4 output fields sized at 220 to handle the content of the four searches, when the Parse . the input field can be found in any of the columns of the database. The append operation creates the . Data Ty