Alteryx Designer Desktop Knowledge Base
Definitive answers from Designer Desktop experts.Using Conditional Statements to Change your Data
A large component of data blending is applying mathematical or transformational processes to subsets of your data. Often, this requires isolating the data that complies with certain criteria that you’ve set:
- “I only want to flag the SKUs for products whose cost is less than $10”
- “Clients between the ages of 25 and 40 should be categorized as Group A, 41-65 as Group B and 66+ as Group C”
- “Categorize transactions as Weekday or Weekend”.
- “If a product is Red, rename it R. If it’s Blue, rename it B. If it’s yellow, rename it to Y. If it’s Green, rename it to G. Otherwise, rename it Other”.
Do any of these situations sound familiar? If so, then a good place to start transforming your data is the Formula Tool’s Conditional functions (Figure 1). The Conditional functions build expressions that allow you to apply processes to data that satisfy conditions you set.
No matter if you come from the world of SQL, Excel, R or another data program that has been making your life harder than it should be, the concept of the Conditional statement remains the same: If a condition is or is not true, then apply a process or result. Otherwise, apply a different process or result. That logic can take the format of one of four different types of expressions:
1) A traditional IF statement can be a powerful tool in data transformation. Structurally, Alteryx requires four distinct clauses to apply this type logic to your data:
IF a condition is (not) true THEN apply Function A ELSE apply Function B ENDIF
Using the first situation (SKUs and Prices) as an example, the IF statement allows us to determine which products will be flagged for further analysis:
2) A nested IF statement allows for multiple conditions to be set, essentially chaining logical statements together, using repeated logical statements similar to a single IF statement. Though the documentation provides an example with three set conditions, this is by no means a limit in the number of criteria that can be set. As long as the correct syntax is used, many more conditions may be set!
Consider the second situation (Client age groups). By continuing with the correct syntax, four groups (A, B, C and Other) are created as a result of the nested statement. When working with nested statements, you may find it helpful to break each clause into a new line in the Expression box, as shown in the example below.
3) Though In-line IF (IIF) statements apply similar logic to an IF statement, evaluating a condition to be either ‘true’ or ‘false’, they differ syntactically. IIF expressions require three components, each separated by a comma: a Boolean expression to evaluate, the “True” result, and the “False” result. The True and False results support not only text but also mathematical operations. In the case of the third situation, where a record can only be classified as one of two types (Weekday or Weekend), an IIF statement effectively categorizes the data according to a logical of test of whether the value of [Day] is not ‘Sat’ or ‘Sun’. The true result to this statement returns ‘Weekday’; the false result is ‘Weekend’.
4) The Formula Tool’s Switch function is a hidden gem! Part Find/Replace tool, part nested IF statement, part CASE statement in SQL…it’s handy! This expression evaluates multiple conditions to assign a designated result. If no condition is met, a default value (Value) is set. The Switch function offers a couple of advantages over similar functionalities in Alteryx. First, it grants much of the flexibility of a nested IF statement without the need for repeated IFs, THENs, ELSEIFs….etc. Second, it can serve a similar function as a Find/Replace tool without having to create a second instance of all the data you want to find and the corresponding data used as the replacement. Save myself some time-consuming and error-prone typing? Sign me up!
Note: The types of operators that can be used in an expression depend on the Data Type of the Output Field. If writing to String (or other text type) field, the result will require quotes (single or double) around it. Note how Group ‘A’ is encapsulated by single quotes. Numeric fields, on the other hand, do not require quotes around the result.
*Attached workflow is compatible with Alteryx Designer v10.0 and above.
#
I am back to this article with one query,kindly help for this below condition
If [something]=1 then "it is one"
Else [Do Nothing]
endif
I want my cell value should be blank.(Adding new column formula tool).i am not able to achieve [Do Nothing] this part.
This is possible by "find replace " ,but I don,t think that's the solution.
Regards,
subhajit
@Shelly3 there's a string function for that! Any time you're trying to manipulate text, I would scan the String Functions to see if there's a function that does what you need with the Formula tool.
ReplaceChar([Field1], '_', 'W')
