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.
Data blending, transformation and cleansing..oh my! Whether you're looking to apply a mathematical formula to your numeric data, perform string operations on your text fields (like removing unwanted characters), or aggregate your spatial data (among many other things!), the Formula Tool is the place to start. With the examples provided below, you should be on your way to harnessing the many functions of the Formula Tool:
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.
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 do you convert a string with a $ sign into a number?
1. First remove the dollar sign and commas out of the string
2. Convert () to a negative sign "-"
3. ToNumber() function
There are many ways to do this in Alteryx, here are a few:
Formula Tool - ToNumber(Replace(Replace(Replace(Replace([Data], '$', ''), ',' , ''), ')' , ''), '(' , '-'))
Formula Tool - ToNumber(REGEX_Replace(REGEX_Replace([Data], "\$|,|\)", ""), "\(", "-"))
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.
If I have a list of dates, how can I find the date of the next Monday for each date?
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!
Alteryx has the ability to calculate the quarter, week number, etc:
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
Visit our help documentation for detailed information about Date Time Functions.
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!
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!
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.
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
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.
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.
DateTimeAdd([Date], -1, 'month')
How do I pass a parameter/constant in a workflow?
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.
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.
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:
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.
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: