cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

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
Full details on the addins can be found on my   blog; the addins can be download from   GitHub. Hopefully it will make creating some formulas easier!
View full article
Alteryx is designed to use all of the resources it possibly can. In order to make Alteryx run as fast as possible, it tries to balance the use of as much CPU, memory, and disk I/O as possible.   The good news is that most of the resource utilization can be controlled. You can limit the amount of memory that is used on a system, user, or module level.   The Sort/Join memory setting is not a maximum memory usage setting; it’s more like a minimum. One part of Alteryx (sorts) that benefits from having a big chunk of memory will take that entire amount right from the start. It will be split between all the sorts in your module, but other tools will still use memory outside that sort/join block.  Some of them (e.g. drive times with a long maximum time) can use a lot.   If a sorting can be done entirely in memory, it will go faster than if we have to fall back to temp files, so that’s why it’s good to set this higher.  But if the total memory usage on the system pushes it into virtual memory, you’ll be swapping data to disk in a much less optimal way, and performance will be much worse and that’s why setting it too high is a bigger concern.   The Default Dedicated Sort/Join Memory Usage can be found in the Designer at Options > User Settings > Edit User Settings Best Practices on Memory Settings 32-bit machines*: Setting should be on the lower, conservative side. No matter how much actual RAM is there, only has at maximum 1 GB available, as soon as it is set higher, the machine will cross over into virtual memory and be unable to recover.   A 32-bit machine should never have a setting over 1000MB, and 512 is a good setting. Set it low (128 MB), especially when using Adobe products simultaneously with Alteryx. 64-bit machines: Set this in the system settings to half your physical memory divided by the number of simultaneous processes you expect to run.  If you have 8 GB of RAM and run 2 processes at a time, your Sort/Join memory should be set to 2GB. You might set it lower if you expect to be doing a lot of memory intensive stuff on the machine besides Alteryx   Set your Dedicated Sort/Join Memory Usage lower or higher on a per-module basis depending on the use of your computer, doing memory intensive non-sort work (i.e. large drive-times) then lower it, doing memory intensive sort-work then higher. *Please refer to this link for additional details on 32-but support for Designer
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
Question  How do you round a number up?   Answer Be sure to first understand that different people/organizations all have different definitions on what they mean by rounding.  Once you are clear on what you need, you can use Alteryx to round your numbers.   Example 1: Divided by the place you want to round to, use CEIL to round up, then multiply back by the place:   324 to 400 CEIL([Number] / 100) * 100   Example 2: Ceiling and floor just go to the nearest integer.  The ROUND(x, multiple) function will allow you to specify the level of precision on which to round, very similar to Excel.  But you can't specify the direction, so in your example if your did ROUND(3425.123,1000) it would return 3000.   Example 3:  Please see the attached v10.0 workflow which is more dynamic. The text input identifies the mapping for the conversion joined with length of source input - then an expression is applied in the Formula Tool.         T he final output.      More on rounding.
View full article
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’     The last couple times I’ve helped troubleshoot this it came down to a field or fields in the data that ultimately needed to be a V_WString data type in Alteryx to accommodate for a some Unicode characters (think Japanese or Chinese characters, symbols like ♥ or © or even letters like ñ and ó).   Sometimes it may not be easy to identify fields with those characters. “So how do I find them?” you ask.   Here are a couple things to try: If your table isn’t too large scan through it to see if anything pops out. Try saving each field out as a file all to itself. If a file is 30k in size it is worth investigating that field of data. To try this method use a Select Tool to select one field at a time and then save it to a .tde file.  The results may look like this:   So the possible trouble column has been identified, now what?  Examine the field to see if there are any characters that may be ‘out of the ordinary’. Then in either an existing Select Tool or by adding a new one, find the field in question and change its data type to V_WString and try saving it to a .tde again.  If the file size is different that 30k it may have corrected the issue.     So what if there are too many fields in the data to save them out one by one manually? Batch macro to the rescue!  Create a batch macro to loop through each field and save it out to an individual file for examination.   A workflow is attached (created in Alteryx 10.6) that shows an example of a file that saves incorrectly to a .tde, that is fixed and saves correctly to a .tde and includes an example of a macro that saves each column as its own file.
View full article
A flat file was parsed into a number of fields, including one field that needs to be converted to a numeric data type.    It looks like this in the data:  000000437 000012587   The resulting number is supposed to have four decimal points, so just applying ToNumber([Field]) won't work because it will convert  000000437 to 437 but the required output is 0.0437.     As always, there are a couple different ways of going about this:    1. Using the Substring (x, start, length) function, which returns the substring of [x] starting at [start], stopping after [length]. This way, the string can be broken into the first 5 characters and the last 4 characters and a decimal point inserted in between the two. Then, the ToNumber() function can be used as usual.    2. Using the  ToNumber (x, bIgnoreErrors, keepNulls) functions creatively. ToNumber will return 437 for the first row of data and  12587 for the second one . However, if the number is then divided by 10,000, it will return 0.0437 which is the correct result.   3. Using Regular Expression. Regex can recognize a pattern and replace it with another value. In this case, regex can find the last 4 characters of the string: (\d{4})$ and replace them with a decimal point + the pattern it matched to: \.$1       Also take a look at the attached workflow to illustrate the solutions. 
View full article
Scenario   You have a dataset containing information on customers’ survey responses (Y/N), the Customer Segment (Corporate, Small Business, etc) to which they belong, and other location data.  You have been tasked with finding the percent of each Responder type in the entire data set.  To perform these calculations, we’ll need two types of counts of data.  The first is a “conditional count”, or a count of records that meet certain criteria.  The second is a count of all records in a dataset.  Alteryx has two nifty ways to help us obtain these values.  We’ll use both the Count Records and Summarize tools to help us with these tasks!   Methods          Use the Summarize Tool’s “Count” function The Summarize tool allows us to count the number of records that meet certain criteria.  For our particular examples, we want to find the number of records for each Responder Type, Yes or No.  We’ll use the Summarize tool  to Group by “Responder”.  Then, we’ll Count the number of Customer IDs for each Responder type (Figure 1).   Figure 1: The Summarize tool will Count all records, Count NonNull records, CountDistinct (Unique) records and CountDistinct (NonNull) records.      Want to drill down in your data even more?  How about find the number of Responder Types per Customer Segment?  Again, the Summarize tool can help!  Group by “Customer Segment”, then by “Responder”, then Count the “Customer IDs”.  See the attached workflow to see this example in action.   Use the Count Records Tool To calculate the percent of each response type for our entire dataset, we’ll need to know the total number of responders in our dataset.  While there are a few ways to go about getting that number, I’ll highlight the use of the Count Records Tool (well, macro, technically).  “The WHAT?” you ask?  I’ve heard that before.   The Count Records Tool.  It’s easy to miss and I can count the number of times I’ve seen this tool in a user's workflow on one hand.  However, it’s one of those tools for which you quickly find so many uses!  It does exactly what its name suggests: it counts the number of records from an incoming data stream.  The tool itself requires no configuration.  Simply insert the tool into your workflow and receive a count of the number of records in the incoming dataset, even if there are zero records from incoming data* (Figure 2): *The Count Records tool will return a record with value of 0, whereas the Summarize tool will simply not return any records.    Figure 2: The Count Records tool has no configuration and returns the number of records (rows) from an incoming data stream.    Now that we have the counts that we need for our calculations, we're ready to move forward with our data analysis!  Please see the attached workflow for the completed demonstration of this process.             
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
Let's say you have data like this and you'd like to remove the numbers at the end:  Blue4509 Yellow2345 Orange2315 Blue6754     You could use  Left(x, len), like this:  left([Data], (length([Data]) - 4)) (This calculates the length of the field, subtracts 4 and then takes that many characters from the left. E.g.: length(Blue4509) = 8, 8-4 = 4, left(Blue4509,4) = Blue)   Or a combination of ReverseString(Str) and  Substring(x, start, length) :  ReverseString(substring(ReverseString([Data]),4,100)) (This reverses the string, uses a substring to start at the 5th position [positions are zero based], takes the next 100 characters, and then reverses it back. E.g.: reversestring(Blue4509) = 9054eulB, 5th position = e, next 100 characters = eulB, reversing it back results in Blue)     BUT what if your data looks like this:  Blue4509 Yellow2345 Orange231 Blue6754 Green596828   The above won't work anymore, but ReplaceChar(x, y, z) will: replacechar([Data], '0123456789','') (ReplaceChar(x,y,z) returns the string [x] after replacing each occurrence of the character [y] with the character [z].)   Or  Regex_Replace(string, pattern, replace,icase): regex_replace([Data],"\d","") (Regex_Replace(string, pattern, replace,icase) returns the string after replacing the pattern with the replace. In this case, it replace all digits.)   Or the Regex Tool:    \d in regular expression identifies digits (aka numbers)    What if your data looks like this:  1Blue4509 2Yellow2345 3Orange231 4Blue6754 5Green596828   and you want to preserve the number at the beginning of the string but remove the ones at the end?    The $ character in regex denotes the end of the string, * identifies one or more instances of the preceding character, so \d*$ identifies one or more digits at the end of the string: regex_replace([Data], "\d*$","") or:     Also check out all the other functions on the Alteryx help page, POSIX Extended Regular Expression Syntax, more info on the Regex Tool, and the Regex Cheat Sheet on the community. 
View full article
Let's take a look at this data set that needs to be combined into one field for reporting purposes:   but it shouldn't end up looking like this:    That is no better for reporting purposes than the data we started with because we only want to see areas that need improvement and we don't want to just combine values separated by commas.    So how can we get it to look like this?    A formula such as this one could work:  If contains([ProdDessert], "Improve") then [ProdDessert] else "" endif + " & " + If contains([ProdSupplies], "Improve") then [ProdSupplies] else "" endif + " & " + If contains([ProdFood], "Improve") then [ProdFood]else "" endif + " & " + If contains([ProdFoodSvc], "Improve") then [ProdFoodSvc] else "" endif   But that's a lot of typing! And it will require clean up of "&" and spaces where they aren't needed.     So let's look at a dynamic way of doing this:    First, using aTranspose Tool, the columns are turned into rows and the data is filtered for rows that contain the word "Improve":    Then, using a Summarize Tool, the remaining rows are concatenated together using a space as the delimiter:   Lastly, some cleanup is done in a Formula Tool:    And the data is ready for reporting!     Also take a look at the attached workflow that walks through these steps. 
View full article
Recently a couple questions came across the Customer Support desk asking how a fiscal calendar could be incorporated into a workflow. Natively Alteryx doesn’t have a tool to create one, but Alteryx does have a number of tools to make a fiscal calendar. Here is an example of how this can be done.   Simple Calendar 1. Determine the start and end dates of the fiscal calendar and enter them into Text input tool, each on an individual row   2. Connect a TS Filler tool to generate dates between the start and end dates     3. A Select Tool was added to remove unnecessary fields   4. Add a Multi-Row Formula Tool to create a Day of Week field by assigning each row a day of the week from 1 to 7     5. Add another Multi-Row Formula Tool to calculate the fiscal week. Whenever the Day of Week is 1 add a value of 1 to the previous row’s fiscal week value.  This will create a running week value for the entire year     That's it!   An example workflow is attached.  Also in example workflow is an example of how the fiscal month and week of month may be added.  (Workflow is in Alteryx 10.6 version.)
View full article
Question I have a table of sales data with each column being a week's worth of sales. I only want records that have data in each of those fields and want to filter out all records that have Null values. How can I do this? Answer There are two basic elements necessary to make this happen. The first is that all records in the original table have a unique ID. If you do not have a unique ID in your data, go ahead and add a Record ID Tool.     In the sample data you can see we will want data from Rows 1 and 6 while filtering out each of the other records because they contain null values.   From here we will use the Transpose Tool to pivot your data into 3 separate columns. In the transpose field choose your unique ID as the KEY FIELD and make sure all other records are selected as DATA FIELDS.      The result is that you will have your unique ID field, a field called [Name] which contains the names of each of the fields in your data, repeated for every unique ID in your original data, and a [Value] field which contains the individual values for each of the records for each of the columns in the original data.   Now we want to search for Nulls, and get a comprehensive list of the UniqueID values that do not contain Null values. Now is the time to bring in a Summarize tool and GroupBy your unique ID field, and then use the CountNull action.     The result is a list of how many nulls exist in each of your unique ID groups.   Next we can simply filter out the fields that have 0 null values in them and then use the unique IDs to join back to the original data, and pull only those records.            It's important to note here that because I'm only interested in the original fields I intentionally chose to deselect the unique ID and the Null Count fields from the output of the join so that I am left with only those records that have data in all of the weeks.   See the attached v10.5 workflow for an example of the approach above.
View full article