community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
 You are using an unsupported browser for translation. Please switch to another browser.

The Auto Field tool examines your data, and automatically optimizes the field type and length.  Take a look at your data with a Select tool, follow this up with an Auto Field, and follow that up with another Select tool to see what kind of changes you’ve made.  After you run the module, you can examine each Select tool to garner a before-and-after view of the adjustments made to the fields.  You can even take this a step further, and add a few Browse tools to see how your database actually decreases in size, you may be surprised by how much! In the below view, the file size was reduced by about 40% with the Auto Field tool, on just 50,000 records and one field. Now consider running a file of millions of records and the amount of the size decrease becomes really substantial! Before Auto Field View: After Auto Field View:
View full article
Any tool that performs a Sort will increase processing time.   Many tools in Alteryx utilize a sorting process as a facet of their inherent functionality or if a group by option is used.  The Summarize tool does this, for example.  Alteryx has the ability to remember Sorts, or said another way, it knows if data has already been sorted upstream.  This is a behavior that you can take advantage of as yet another means of optimizing your module’s processing.  In the example shown, the process in Green is more efficient, as sorting will only be performed on the data once.  For the process in Red, sorting will have to be performed twice.   Tools that Sort   Tools that use Sorts: Tools use Temp Files and/or Internal Sorts: Cross Tab Spatial Process Dynamic Input Block Until Done Join Auto Field Multi Row Formula Distance (Guzzler Only) Running Total Trade Area (Guzzler Only or Non-Overlapping) Sample Run Command (Temp files only) Tile   Unique Use Temp Files and/or Internal Sorts at the Process level: Allocate Report Location Optimizers Chart Batch Macros (for secondary inputs) Layout Iterative Macros (for secondary inputs) Render   Table   Behavior Create Profile   Buffer   Find Nearest   Generalize   Poly Build   Smooth   Spatial Match   Summarize   Charting   Join Multiple   Sort   Calgary Loader   Make Group   Fuzzy Match    
View full article
Alteryx provides GUI tools that offer similar functionality to many SQL commands. Although minimal SQL scripting may be necessary in order to properly configure tools, the amount required to complete analysis is significantly reduced.
View full article
 Scenario:  You've been given data for a new project and it contains lots of extra (and unnecessary) rows before you even get to the information you need to work with.  Look familiar?      For many Alteryx users, this situation is all too common.  Luckily, there's a pretty easy way to resolve this issue using the Sample and Dynamic Rename tools!   Method:  To demonstrate this approach, we'll use some sample data that has extraneous information and space at the top (Rows 1-4) of the spreadsheet in Figure 1 (below).  While the information itself might be important, it's going to interfere with our data analysis.  What we really want to see is the information in Row 5 as our header name and the information from Row 6 onwards to be our data.   Figure 1: The data in rows 1-4, as seen in Excel, should not be included in the data analysis.      Rather than manually re-format our dataset, we'll bring it into Alteryx and let the data preparation begin! Using an Input Tool, we'll navigate to the location of our data file.  The tool gives us a preview of what to expect when bringing in the data (Figure 2).  This format is nowhere near perfect, but we still have a few tricks up our sleeve!    Figure 2: The Input Tool shows how the data will be brought into Alteryx.  Our heading is not correct, and we still have a few lines of data (in light yellow) to eliminate while keeping the data we want to analyze (in dark yellow).     A quick visual assessment indicates that we'll need to skip the first three rows of data (the information in Row 4 will become our field names).  We can remove these data using a Sample Tool.  In the Sample Tool configuration (Figure 3), we'll opt to "Skip the 1st N Records"; in this case, N will be equal to 3.   Figure 3: Set the number of records to skip, or remove, from the top of the dataset.   Now that we've removed the first 3 rows of data, we are much closer to the version of the data format we'd like to work with.  The data we'd like to use as the field names (Number, FirstName and State) are now in the first row of data.  We'll use the Dynamic Rename Tool to re-name our fields using the option to "Take Fields from the First Row of Data" (Figure 4).  And, voila!! Our data is now ready to use for the next steps of our analyses.      Figure 4: After removing unwanted rows of data and re-naming the fields, our data is ready for further analyses.    *See the attached sample workflow (v10.5) for an example of this process.     
View full article
Question How can you calculate the standard deviation of the population (SDpop) rather than standard deviation on the sample data (SDsample)? Answer Please find the attached macro, special thanks to one of our Community users!   Standard deviation of the population (SDpop) and standard deviation on the sample (SDsample) are  very similar - the only difference is that in SDpop the sum of the squared variances is divided by n instead of n-1 as it is for SDsample (where n is the number of data points in the vector).  This macro takes a vector of doubles which needs to be named 'dataVals' and it returns the standard deviation of the population.  Accurate out to the 9th or 10th place.  
View full article
If you’ve gotten accustomed to having headers and sub-headers (fields/sub-fields) in your tables, much like those you can build with the “Merge & Center” Excel feature, you’ll be happy to hear with just a couple formatting steps we can build the format below:     In Alteryx, with similar formatting:     The attached workflow goes into the details of the approach, but the essence is below:   Split your columns/fields your desired categories (making them sub-headers) Create tables from your categories, add your supra-headers Combine the reporting elements we have now, use the Union Tool to stack and the Summarize Tool to combine the reporting elements horizontally Combine your sub-tables into a larger table, if needed (adding another header)   There you have it! Your tables are now rendered in style.
View full article
Unlike a snowflake, it is actually possible for duplicates exist when it comes to data. To distinguish whether or not a record in your data is unique or a duplicate we have an awesome tool called the Unique Tool  that will actually turn your data into a unique snowflake.
View full article
Errors from the R tool, and macros using R, such as the Association Analysis tool, can be a challenge to troubleshoot.  Since R is very particular about naming conventions, and the R macros are very particular about delimiters, one easy way to preemptively limit these errors is to make sure your field names are R-friendly and compliant.   Best practices is to use only letters, numbers and the underscore.  Any special characters will likely upset R. One hint is "Error: No valid fields were selected."  This means that one of the Pipe-to-Rows tools embedded in the R macros could not appropriately parse the field names.   For example, the following errors came from the Association Analysis tool, where the field names contain the gamut from asterisks to ampersands:   Error: Association Analysis (4): The Action "select fields A" has an error: ParseError: Expected entity name for reference at line 7 and column 30 while processing "Action_UpdateRawXml" Error: Association Analysis (4): The Action "select fields A" has an error: ParseError: Expected whitespace at line 13 and column 63 while processing "Action_UpdateRawXml" Error: Association Analysis (3): The Action "select fields A" has an error: ParseError: Unterminated entity reference, 'test' at line 4 and column 30 while processing "Action_UpdateRawXml"   If you have a thousand fields with every special character, don't fret.  Dynamic Rename and regex to the rescue!   Using the Rename Mode: Formula, select all your fields, and replace any non-word character with an underscore:     Crisis averted!  Happy Alteryx-ing.
View full article
Sometimes, data will come with multiple header lines, for example year and quarter might be broken into two rows, like this:      That is not very helpful when you are trying to analyze your data.    You could use a Select Tool and type in new names OR you could do a few transformation and use the Dynamic Rename Tool to merge the rows dynamically, so you are prepared when 2017 rolls around the corner and they start adding new columns to your file!    To fix data that looks like the above, we start with the Sample Tool to split the data into two data streams - one with just the values and one with the header information. Then, we transpose the data to let us fill in the missing years and use the Summarize Tool to concatenate the resulting field values:       Now we can use the header lookup we created as a right input into a Dynamic Rename Tool using the "Take Field Names from Right Input of Rows" option and selecting "Name" as the old field name and Concat Value as the new combined field name.    See the attached workflow for details of tool configurations. 
View full article
This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Record ID Tool on our way to mastering the Alteryx Designer:   Here at Alteryx, we do our best to keep things simple. Analytics is hard enough, there’s no need to overcomplicate things. That’s why we have the Record ID Tool – true to its name, if your records don’t have a unique identifier or row count just drag this tool onto the canvas. That’s it. Put your feet up, take a deep breath, and celebrate the win. The best part? The Record ID Tool doesn’t stop there – there’s countless applications of the tool that can simply other operations, too. It’s a gift that just keeps on giving:   Use a Record ID field to create primary keys in database tables created by a workflow Split your output into multiple files using Record IDs to specify precise record counts Process workflows in “packets” of records leveraging a Record ID - in some cases, this decreases run time Compare datasets down to the last record by mapping them to a Record ID Use the modulo (mod) function to make groups of your data from the Record ID field, simplifying otherwise complex reshapes (see examples 1 and 2) You can also enforce a record order to your datasets using a Record ID (just sort by it), which often comes in handy before reshaping or macro processing. If you’re looking to assign “Group By” Record IDs that reset to unique values of a particular field, try using the Tile Tool.   That’s a lot of operations made simpler by a single tool; it could be a record. Now, if that’s not worth celebrating, we don’t know what is.     By now, you should have expert-level proficiency with the Record ID Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.   Stay tuned with our latest posts every   Tool Tuesday by following   Alteryx on Twitter! If you want to master all the Designer tools, consider   subscribing for email notifications.
View full article
Have you ever wanted to do a Cross Tab, but needed the results in a particular order?  You've probably discovered that Cross Tab will dutifully change your data to column headings and keep the rest of the data correlated, but that the output columns will be arranged in alphabetical order.  It's as if there were a Select tool with the "Sort on Field Name" Option thrown into your workflow without you putting it there.    
View full article
Upon creating a BINGO game, I came across a technique that I thought could be useful in "real world" scenarios for users who are attempting to iterate a process and then replenishing the data after a certain amount of time.
View full article
The Arrange Tool allows you to manually transpose and rearrange your data. It can be used to combine multiple fields into a single target field.
View full article
Impute missing data with dynamic values other than the field's mean, median or mode.
View full article
The  Multi-Row Formula Tool  functions much like the normal  Formula Tool  but adds the ability to reference multiple rows of data within one  expression . Say, for example, someone was on the ground floor of a house and had a Formula Tool. They would only be able to talk to the people also on the ground floor. If they had a Multi-Row Formula Tool, though, they would also be able to talk to the people upstairs, in the attic, and in the basement as well.
View full article
The  Filter Tool , which is part of the  Preparation tool category , separates your data into 2 output streams, True and False, based on a basic filter or custom expression.
View full article
Question How do I capture values that are not recognized as dates?  I am importing data from an Excel file.  Two of the fields are supposed to contain dates, but because the values are entered by hand sometimes they aren't entered in a way that is recognizable by Alteryx as a date.  For example, "11/8/`6" is entered instead of "11/8/16".  I want to divert the records that could not be read into a separate workflow. Answer Convert the date field first.  If the date does not convert, a NULL is produced.  Use a Filter Tool based on finding NULLs to create a separate stream:       See v10.5 workflow attached.
View full article
Oftentimes in spatial analytics you’ll need to find the closest spatial object to another. The most intuitive way to do that that is through the Find Nearest Tool, which specifically captures the ability to find the shortest distance between spatial objects in one file (targets) and a user-specified number of objects in another file (universe objects). This tool does an amazing job of simplifying the process of finding the nearest object to another but it can also add significant time to your workflow.   I often elect for an alternative method that has trimmed significant run time off of many of my spatial workflows. That is, using the Append Fields Tool to duplicate your target spatial objects for each universe and using the Distance Tool to calculate DriveTime. After that’s done, simply add on a Summarize Tool, group by target and take the “Min” DriveTime for each. You could also sort ascending by DriveTime and sample for the first target by grouping with that field. There is a caveat, however, as the Append Fields Tool drastically increases the number of records in your input and will only speed up the process if there are significantly more targets than universes.   These methods are distinct in that the Find Nearest Tool must do a DriveTime run from each target spatial object to each universe spatial object (200 DriveTime passes in Example 1) whereas the Distance Tool approach already has all the points available to it and recognizes that there are many more targets than universes. As a result, it runs the reverse-direction DriveTime calculation starting from each universe to all target spatial objects at once (5 DriveTime passes in Example 1). If it is quicker for you to use the Find Nearest Tool, be sure to shed the spatial objects you no longer need in your workflow as soon as possible, even inside the Find Nearest Tool’s configuration if possible. That could also reduce your run time due to the sheer size of the spatial object datatype. Below are some examples of the methods. They can also be seen in the attached workflow, AppendAlternative.yxzp, which was made in Alteryx 10.0.   Example 1 Targets: 200 Universe Objects: 5   Attempt 1: Find Nearest Tool Run Time: 8 minutes 13 seconds   Attempt 2: Append Fields Tool and Summarize Run Time: 11.9 seconds   Example 2 Targets: 100 Universe Objects: 52   Attempt 1: Find Nearest Tool Run Time: 49.7 seconds   Attempt 2: Append Fields Tool and Summarize Run Time: 12.6 seconds   Matt DeSimone Client Services Representative
View full article
Users often ask, "How do I do (x) function in Alteryx?" - here's a handy guide for translating Tableau functions for use in Alteryx!   *Please note: This is not a comprehensive list of all functions available in Alteryx and Tableau - only functions that are written differently, but perform similar tasks, are included here. For a list of all the functions available in Alteryx, please refer to our Help Documentation.   Number Functions  Alteryx  Tableau CEIL(x) CEILING(x) Return smallest integer greater than or equal to [x]. Works like the 'RoundUp' function in Excel. MOD(n, d) x % y Modulo of n divided by d - The Modulo operation finds the remainder of division of one number by another number. POW(x, e) POWER(number, power) Return [x] raised to the [e] power. Round(x, mult) ROUND(number, [decimals]) Return [x] rounded to nearest multiple of [mult]. IF isnull([field]) THEN 0 else [field] ENDIF or Contains([field], "string") ZN(expression) Returns the expression if it is not null, otherwise returns zero. Use this function to use zero values instead of null values.   String Functions IF Contains([field], "string") then 1 ELSE 0 ENDIF Contains(string, substring) Returns true if the given string contains the specified substring. FindString(string, Target) FIND(string, substring, [start]) Searches for the occurrence of a particular string within a data field and returns the numeric position of its occurrence in the string. In Tableau, returns the index position of substring in string or 0 if the substring isn't found. If the optional argument start is added, the function ignores any instances of substring that appear before the index position [start].* Length(x) LEN(string) Return the length of the string [x]. LowerCase(x) LOWER(string) Converts a string to lower case. REGEX_Match(string, pattern, icase) REGEXP_MATCH(string, pattern) Searches a string for an occurrence of a regular expression. REGEX_Replace(string, pattern, replace, icase) REGEXP_REPLACE(string, pattern, replacement) Allows replacement of text using regular expressions and returns the string resulting from the RegEx find pattern and replace string. Substring(x, start, length) MID(string, start, [length]) Return the substring of [x] starting at [start] and stopping after [length], if provided.* TRIMLEFT(x, y) LTRIM(string) Remove character in the string y from the end of the string x; y is optional and defaults to trimming white space. In Tableau, this function trims extra whitespace. TRIMRIGHT(x, y) RTRIM(string) Remove character in the string y from the end of the string x; y is optional and defaults to trimming white space. In Tableau, this function trims extra whitespace. Uppercase(x) UPPER(string) Converts a string to upper case.   Date Time Functions DateTimeAdd(datetime, interval, units) DATEADD(date_part, interval, date) Return the given date/time modified by the given duration. The <interval> specifies a positive or negative integer of time to add or subtract and <units> is one of a date/time unit - "years", "months", "days", "hours", "minutes", or "seconds". For Tableau, additional date_part units are allowed. DateTimeDiff(datetime1, datetime2, units) DATEDIFF(date_part, date1, date2, [start_of_week]) Subtract the second argument from the first and return it as an integer difference. The duration is returned as a number, not a string, in the specified units - "years", "months", "days", "hours", "minutes", or "seconds". For Tableau, additional date_part units are allowed. DateTimeTrim(datetime, trim type) DATETRUNC(date_part, date, [start_of_week]) Remove unwanted portions of a date/time and return the modified date/time. Options include: firstofmonth, lastofmonth, year, month, day, hour, minute. In Tableau, truncates the specified date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month. The start_of_week parameter is optional. DateTimeNow() NOW( ) Returns the current system date and time. DateTimeParse(datetime, format of incoming string) MAKEDATE(year, month, day) Converts a date string with a specific format to the standard ISO format yyyy-mm-dd HH:MM: SS. In Tableau, returns a date value constructed from the specified year, month, and date. DateTimeToday() TODAY( ) Returns today’s date. The time is set to midnight of the beginning of the day. Returns the current date.   Conversion Functions ToNumber(x, bIgnoreErrors, keepNulls) INT(expression) or FLOAT(expression) Converts a string parameter to a number. The second parameter is optional and allows for ignoring conversion error messages. This parameter is a boolean flag and will accept a value of 1, 0, true or false. There is an optional 3rd parameter to handle Nulls. In Tableau, INT casts its argument as an integer. For expressions, this function truncates results to the closest integer toward zero. FLOAT casts its argument as a number with decimal/float precision. ToString(x) STR(expression) Casts its argument as a string.   *In Alteryx, string positions start at 0. In Tableau, string positions start at 1.
View full article
Binary (bit level or bitwise) operations operate on one or more bit patterns or binary numerals at the level of their discrete bits. They are typically used to manipulate values for comparisons and calculations - and encryption!   These functions are in Formula Tool, in the Integer submenu of the Math menu:     Binary values are stored as strings, retaining the leading zeros. To use these functions, you'll have to convert these to numerals.  Use the BinToInt() function to convert your binary strings to numeric values.  Once you've completed your calculations, use IntToBin() to get the binary values.  Note: you'll need to add your leading zeros back in, using the PadLeft function.    E.g.:  PadLeft(IntToBin([converted_binary]),8,'0')   If you need the character associated with the binary value, use CharToInt().  Hex calculations work similarly.     BinaryAnd(n,m) – performs the logical AND operation on two binary numerals   BinaryNot(n) – performs logical negation, forming the complement (bits that are 0 become 1, and those that are 1 become 0)   BinaryXOr(n,m) -  exclusive disjunction essentially means 'either one, but not both nor none'. In other words, the statement is true if and only if one is true and the other is false.   A common use for XOR is a means of doing a parity check. A bitstring has even parity if the number of 1s in the string is even. It has an odd parity if the number of 1s is odd. If you XOR the bits together, you can tell whether a bitstring has even or odd parity.   ShiftLeft(n,b) / ShiftRight(n,b) - shifting left is equivalent to multiplication by powers of 2. So 5 << 1 is equivalent to 5 * 2, and 5 << 3 is equivalent to 5 * 8.  Shifting to the right is equivalent to division by powers of 2.   Please see the attached v10.5 workflow for a simple secret message conversion.   Happy Alteryx-ing!
View full article