cancel
Showing results for
Did you mean:

# Alteryx Knowledge Base

## Tool Mastery | RegEx

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 .

## How to Find the Next Specified Day from a List of Dates

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!

## How to Trim Numbers: Regex to the Rescue

How to Trim Numbers with a RegEx formula

## How To: Calculate the week number, quarter, month start, month end

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.

## Changing Single Quotes to Double Quotes in an Expression

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!

## Search for Keywords in a Text String

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!

## Rounding in Alteryx Designer

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.

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.

## How To: Create a new date field based on a Formula

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')

## Parameter (Constants)

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.

## Marquee Crew's Guide to Dates

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.

## Writing Formulas to Excel

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:

## Custom Formula Functions

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!

## Scientific Notation in the Text Input Tool

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.

## Tool Mastery | Error Message

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.

## Tool Mastery | Multi-Row Formula

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.

## Tool Mastery | Filter

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.