This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
First added to Alteryx Designer in version 10.6, the Optimization Tool is a member of the Prescriptive Tools (included with the Predictive Tools installation) and allows you to solve optimization problems. Mathematical Optimization is the selection of the best possible option(s), given a set of alternatives and a selection criterion. In this Tool Mastery, we will review the inputs, configuration, and outputs of the Optimization Tool.
A large component of data blending is applying mathematical or transformational processes to subsets of your data. Often, this requires isolating the data that complies with a certain criteria that you’ve set. The Conditional functions build expressions that allow you to apply processes to data that satisfy conditions you set.
Every so often we get questions about a .tde (Tableau Data Extract) file that is being output from Alteryx that has a file size of 30k when the original data is much larger. When the file is opened in Tableau this error sometimes comes up: An error occurred while communicating with data Source ‘yourfilename.tde’
For any macro or analytic app – one of the inevitable questions that you may encounter is “how do I configure this to do what I need?” For example, if you build a macro that checks if two fields are equal, but sometimes you want to ignore the case such that “A” equals “a,” and sometimes you want an exact match. This is where the Interface Tool Category comes to the rescue, with a super-tool called Check Box!
Did you ever play that party-game called 6-degrees of separation where you have to figure out how you are connected to someone famous in less than 6 relationships (or the movie version – 6 degrees of Kevin Bacon)? Well that game just got a whole lot easier with the Make Group Tool!
Any time you want to get a good point across, it’s best to show your data. Show your data off in style in your reports or presentations by adding formatting to otherwise bland data with the Table Tool! Found in the Reporting Tool Category, the Table Tool will make it easy flair to your raw data, and give it the pop it needs to really sink in.
You've gotten your long dataset and you want to combine it with another dataset for additional information. Your dataset is nice and clean. Everything is formatted the same, no null values... The whole package. You open up the data to join to and right away you see a ton of clean up that needs to happen: nulls to replace, strings to format appropriately, extra characters, white space, the list goes on. You launch the Designer, and while fast and accurate, you have to set up a new Multi-Field Formula Tool for each situation you need to fix. If only there was a single tool that did it all.
RegEx….it can be tough, but extremely useful when looking for ways of extracting information from a string. Regular Expressions are basically a code you can write to match a specific set of characters (it's a pattern matching syntax). It could be something as simple as finding the three digit number in the string “Eample123Eample” or something more complicated like using hexadecimal to select a certain range of characters. RegEx is something that can be used in Alteryx via the RegEx tool in the Parse tool set and some of the main uses for RegEx are replacing, matching, and parsing specific characters within a string. Replacing text is a very handy process when working with string fields, as it can be used to replace virtually any set of characters. In the attached example module you will find the following data in Text Input Tool: What I would like to do for this example is replace the portion of the string “file” and only that portion. The easiest was to do this using the RegEx tool is to follow these steps:
Make sure that the correct field is selected in the Field to Parse drop down.
Enter “file” as my RegEx in the Regular Expression text box.
Make sure that your Output Method is set to Replace.
Finally, enter a replacement text in the Replacement Text text box.
In this example, I choose “FileNumber_”. Once a browse is connected and the module is run, you can see that the output have replaced all of the character matching “file” with “FileNumber_”. Using RegEx to match is another process that can be very helpful for your data preparation or transformation process. Using the Match output method will assign a True or False value for you data based on the RegEx that you have written. I’m going to use the file initial data that was used above, but this time I was to match only the records that are “file1…”, “file2…”, and “file3…”. To do this, I have written a regular expression of: file  .+ . The breakdown of the RegEx script is as follows:
First, I am searching for “file” much like I did in the replace example.
Next, I am using brackets “”, to only look for the numbers 1, 2, or 3. Square brackets are used to match specific characters, just like an OR statement in a Formula Expression.
And lastly, “.+”. The period will look for any character and the plus will look for one or more additional characters. This covers the file extension in our data.
As you can see in the below output, only records that had a file name of file1, file2, or file3 have been returned as a True match, while file4 returns False: Parsing with the RegEx tool can be a little bit more complicated, but again it is an extremely useful process that will help in your data prep and transformation stages. The nice thing about parsing with the RegEx tool is that it also allows you to alter the fields upon output. Once you have a valid regular expression for parsing, you will be able to change the field name, the data type, and the size of the field. The trick to parsing using RegEx is that you have to create a group, and this is done by using parenthesis “()” to identify each group. You can use multiple group parse into multiple field, but for this example there will just be one group. Using the same initial file name field, we will use a regular expression to parse just the file name without an extension. For this I have used the following expression: ^(.+) . .+$ . Just like before, the breakdown is as follows:
Using a “^” in RegEx means to look at the start of the string. This portion “(.+)” is looking for my group. In this case, any number of characters. And since I added a “^” to the beginning, it will look at the beginning of the string.
The ".." portion will look for the period in between the file name and the extension. Using a slash before the period will tell RegEx that you are actually looking for a period (the slash is called an Escape character). Otherwise a period is a wild card, meaning it will return any character.
The "+$" is the last portion where we look for the extension. Using “.+” will look for any characters, which will account for all of the extensions, while the dollar sign is making sure that this is looking at the end of the string.
Here is the outcome of using the parse method to find just the file name: I hope this helps you get started with regular expressions! As always, if you have any questions please don't hesitate to email email@example.com. Special thanks to Mike Akey for putting together this post, until next time!
While the Join tool is easily one of the most used tools in Alteryx, it can also be one of the most misunderstood. This is even more likely true if a new user hasn’t previously used joins in any other data-manipulating platform or they are joining big tables where they might not be keeping track of the records inside the fields they are joining on.
Sometimes, a dataset will contain numbers stored as text. I order to do calculations using those numbers, the datatype will need to be converrted to a numeric data type. If the data is clean, changing the data type in a select tool can do the trick. Another option is to use the TONUMBER() function in a formula tool or multi field formula tool (if you have more than one field to convert).
You're working on your gazillionth Formula tool and "Jeff" from Quality Assurance sends you an email that the margin of error for your process has been restandardized. It's not 0.122 anymore but 0.121 . Then the horrible reality sets in - you're going to have to go back through all of your formulas and update that one.. stupid.. little.. number .
One of the best things about Alteryx is the ability to read in multiple files very easily and automatically combine them into a single dataset. This becomes a bit trickier when dealing with files that have different schemas or Excel files with multiple tabs. Adding both multiple excel files with multiple tabs, and having the schema change within each tab takes it to another level.
For most tools that already have “dynamic” in the name, it would be redundant to call them one of the most dynamic tools in the Designer. That’s not the case for Dynamic Input. With basic configuration, the Dynamic Input Tool allows you to specify a template (this can be a file or database table) and input any number of tables that match that template format (shape/schema) by reading in a list of other sources or modifying SQL queries. This is especially useful for periodic data sets, but the use of the tool goes far beyond its basic configuration. To aid in your data blending, we’ve gone ahead and cataloged a handful of uses that make the Dynamic Input Tool so versatile:
The Fuzzy Match Tool provides some pretty amazing flexibility for string joins with inexact values – usually in the case of names, addresses, phone numbers, or zip codes because many of the pre-configured match styles are designed around the formats of those types of string structures. However, taking advantage of the custom match style and carefully configuring the tool specific to human entered keyword strings in your data can also allow you to use the loose string matching feature of the tool to match those values to cleaner dictionary keyword strings. If done properly, it can help you take otherwise unusable strings and, matching by each individual word, recombine your human entered data to a standardized format that can be used in more advanced analyses.
Ever have to output tables of differeing schemas to the same Excel workbook? Ever need to output to different tabs? This article covers your bases with the cunning use of Reporting tools! Also included are links to other helpful "outputting to Excel" Knowledgebase Articles.