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

Alteryx Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
The RegEx tool is kind of like the Swiss Army Knife of parsing in Alteryx; there are a whole lot of ways you can use it to do things faster or more effectively, but even if you just use the blade it's still immensely useful. Sometimes that's all you need, but if you do take the time to figure out how to use a few other tools in that knife, you'll start to see that there isn't much you can't do with it .
View full article
Question If I have a list of dates, how can I find the date of the next Monday for each date? Answer Using a simple workflow, you can calculate the next Monday from any date by using a single Formula tool and configuring as follows: Determine the day of each date Day: DateTimeFormat([Sample Dates],"%a") Calculate the number of days to get to the next Monday based on each day AddDays: Switch([Day],Null(),'Mon',7,'Tue',6,'Wed',5,'Thu',4,'Fri',3,'Sat',2,'Sun',1) Add the number of days (from step 2) to get to the next Monday to each date Monday: DateTimeAdd([Sample Dates],[AddDays],"days") Verify that new date is Monday VerifyNewDay: DateTimeFormat([Monday],"%a") You can actually do all of this within one formula, save for verifying the day, if you want to get fancy: Monday: DateTimeAdd([Sample Dates], Switch(DateTimeFormat([Sample Dates],"%a"),Null(),'Mon',7,'Tue',6,'Wed',5,'Thu',4,'Fri',3,'Sat',2,'Sun',1),"days")   Things to consider: This workflow assumes that your dates are already in the Alteryx native date format of "YYYY-MM-DD". If they aren't, please visit another Knowledge Base article, Date Conversions, for tips on how to get your dates there!   This was done in 10.1. Sample workflow attached.   Thanks for tuning in!
View full article
How to Trim Numbers with a RegEx formula
View full article
Alteryx has the ability to calculate the quarter, week number, etc:   Example  The attached workflow uses several of the Date Time functions:  (See attached workflow) DateTime FirstOfMonth() Returns the first day of the current month, at midnight. DateTime LastOfMonth() Returns the last day of the current month, with the clock set to one second before the end of the day (23:59:59). Quarter formula - if [Month] IN (1,2,3) THEN [Year]+' '+'Q1' elseif [Month] IN (4,5,6) THEN [Year]+' '+'Q2' elseif [Month] IN (7,8,9) THEN Year]+' '+'Q3' else [Year]+' '+'Q4' endif Week Number - ToNumber(DateTimeFormat([Date],'%U'))+1   Input:   Output:   Visit our help documentation for detailed information about Date Time Functions. 
View full article
Suppose you need to change single quotes to double quotes in an expression; i.e. write a formula in an Action, Condition, Filter, Formula, Generate Rows, Multi Field Formula, Multi Row Formula, Charting, Report Map, Table, Message, or Test tool.   To do this, you use the REPLACE function. This is a string function in the format:   REPLACE([FieldName], ‘x’, ‘y’)   where ‘x’ gets replaced by ‘y’ in [FieldName].   Generally, it is not a good idea to mix single quotes with double quotes as this can sometimes produce unexpected results. In other words, avoid using this syntax:   REPLACE([FieldName], ‘x’, “y”)   You might be tempted to write your REPLACE expression like this:       But this gives you the ‘Malformed Function Call’ error message.   At this point, you may think the double quotes need to be single quotes like this:   But, again, you get the ‘Malformed Function Call’ message.   For this expression to work correctly, the expression should be written like this:       In other words, the single quote needs to be wrapped by double quotes, and the double quote needs to be wrapped by single quotes!     
View full article
This article features a workflow that will allow you to search for keywords within a column of text strings. Potential applications for this might be to scan a Twitter feed, customer product feedback or Facebook comments for keywords such as the mention of particular people, descriptive words or subject matters. For this example, the purpose will be to search famous science quotes for a select group of keywords.   In the first section of the workflow, we are performing the following functions: Reading in the data -  The first table contains the text strings to be searched (quotes); the second table contains the key words that we want to search for. Adding a Record Id in order to join the original text strings back into the data. Using the Text to Columns tool, we will split the text strings into individual rows for each word within each text string. Append each of the key words to each of the records/words resulting from the previous step.   In the next section of the workflow, we will: Use the Multi-Field Formula tool to change all words in both the text strings and keywords to uppercase as the FindString function used downstream is case sensitive. Use the RegEx tool to remove non-word characters from the text string words (such as periods, commas, etc.) so that we can do a double-check on the FindString by using a length test. This will prevent the FindString function from returning a match of the keyword 'know' to the text string 'knowledge'. Match words using the Formula tool and the FindString function in conjunction with comparing the length of the keywords to the text string words.   In the final section of the workflow, we will complete the steps of: Summarizing the counts of each keyword to each Record ID of the text strings. Joining back in the original text strings. Sorting the data back to its original state. Transposing the data in order to perform a cross-tab count of the keywords. Performing a final summary count of the keywords.   Things to consider:   Your text strings might contain non-word characters not accounted for in the RegEx in this example. Make adjustments to the RegEx as appropriate. The workflow results can be used to make a Word Cloud As with almost anything Alteryx, there is more than one way to do things. Try some variations and post anything you think is worthwhile!
View full article
  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
Question How do you convert a string with a $ sign into a number? Answer 1. First remove the dollar sign and commas out of the string 2. Convert () to a negative sign "-" 3. ToNumber() function   Input:     Output:     There are many ways to do this in Alteryx, here are a few:   Example 1 Formula Tool -  ToNumber(Replace(Replace(Replace(Replace([Data], '$', ''), ',' , ''), ')' , ''), '(' , '-'))   Example 2 Formula Tool - ToNumber(REGEX_Replace(REGEX_Replace([Data], "\$|,|\)", ""), "\(", "-"))   Example 3 RegEx Tool #1 - Replace \$|,|\) with Replacement Text "blank", then  RegEx Tool #2 - Replace \(  with Replacement Text "-" Select Tool - Change Type to Fixed Decimal   See attached workflow.
View full article
  How to: Create a new date field based on a formula?     For example, let's say I want to calculate one month prior to an existing date.  i.e. 6/1/15, month prior is 5/1/15.   To do this, I'll add a Formula tool to the canvas, connect it to your data stream, and use the DateTimeAdd() function.   Expression:   DateTimeAdd([Date], -1, 'month')    
View full article
Question How do I pass a parameter/constant in a workflow? Answer You can use a workflow constant to pass a parameter.  Click on the Workflow - Configuration - Workflow tab.  Click the + button to add a new User type constant, and give your constant a name and value!       It is quite common to hardcode values (‘constants’) in workflows using any of the following ways:  Text input tool  Variables within formula tool  Hardcode the values within formula/any other transformation tools  If the values are hardcoded within tools, especially in very large or complex workflows, maintenance can be difficult. The ‘constants’ property allows us to define all static variables in one place, so that they are accessible throughout the entire workflow.       
View full article
Lookup the term "date" in community and you'll get 2,000 results.  Hidden in this article are Easter eggs for the finding.  Besides learning about dates, see if you can uncover the clues that I've cloaked.
View full article
When writing to Excel, have you ever had the need to populate a column with values that come from formulas referencing other cells in your data? You’ve probably noticed that when writing to either .xlsx or .xls Excel file formats that your output still appears as a string, rather than a calculated value, like the below:   .xlsx Each formula cell has the proper cell references and syntax but will need to be interacted with (add or remove a space in the formula and hit enter on your keyboard) to calculate the true formula value.   .xls When expanding on the string you’ll see there’s an apostrophe (‘) preceding the formula and you’ll need to remove each and hit enter on your keyboard for the formula to execute.     In the attached v10.1 workflow, Writing to Excel with Formulas.yxmd, we provide a sample formula expression to build formulas with basic cell indexing (using a record ID field) and the ability to write formulas that can be interpreted by Excel. This is done by leveraging the .csv file format; when excel opens this output file type it automatically recognizes the formula syntax and will interpret it as such – populating each cell with the true formula calculated value:  
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
The “D” notation is common to Fortran implementations and generally represents double precision numbers. This notation is much less common than the E notation, but Alteryx does recognize it.
View full article
A must-have for any app or macro, the  Error Message Tool  displays a prompt to the user based on input from  Interface Tools . Using any expression that evaluates to true, along with any number of user inputs from question  anchor connected Interface Tools, the Error Message Tool can layer even the most involved applications with failsafes that ease a user’s experience through more robust interactions.
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
Is it possible to add a comment to an expression used in a Filter or Formula?
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