Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Using Conditional Statements to Change your Data

ChristineB
Alteryx Alumni (Retired)
Created

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.

 

2018-11-14_13-09-49.png

 

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:

 

 

2018-11-14_13-12-17.png

 

 

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.

 

2018-11-14_13-48-43.png

 

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’. 

 

2018-11-14_13-59-35.png

 

 

 

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!

 

2018-11-14_14-07-04.png

 

 

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.

 

2018-11-14_14-11-43.png

 

 

 *Attached workflow is compatible with Alteryx Designer v10.0 and above. 

 

 

#

Attachments
Comments
SpencerS
5 - Atom

I have a column if it meets the condition to change the content to something else.  That portion works fine.  However if false, don't do anything to the content.  What should following ELSE?  thank you

RodL
Alteryx Alumni (Retired)

If you are wanting to keep what's in the field if the condition isn't met, then you would just put the name of the existing field there.

For example,

If [DataField] > 20 THEN 1.5 * [DataField] ELSE [DataField] ENDIF

SpencerS
5 - Atom
Thank you, that works.

The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this message and any attachments in error and that any review, dissemination, distribution, copying or alteration of this message and/or its attachments is strictly prohibited. If you have received this message in error, please notify the sender immediately by electronic mail, and delete the original message.
jaycaplan
6 - Meteoroid

Thanks so much for this explanation.  Really helped me figure out "find and replace" issue.

dpuls
7 - Meteor

I'm pretty new to Alteryx, started with ChristineB's interactive videos which are terrific.  Thanks for providing all those.

 

In completing the Weekly Challenge #2, I tried to create a new field in the Formula tool using SWITCH  to interpret the postal codes against the provided ranges:

Switch([Postal Area],Null(),
[Postal Area]>=2000 AND [Postal Area]<2020,"R1",
[Postal Area]>=2020 AND [Postal Area]<2040,"R2",
[Postal Area]>=2040 AND [Postal Area]<2060,"R3",
[Postal Area]>=2060 AND [Postal Area]<2080,"R4",
[Postal Area]>=2080 AND [Postal Area]<=2100,"R5")

 

Executed w/o errors but the resultant field was all [Null]s.  After struggling with it, I ended up using nested IFs:

IF [Postal Area]>=2000 AND [Postal Area]<2020 THEN "R1"
ELSE
IF [Postal Area]>=2020 AND [Postal Area]<2040 THEN "R2"
ELSE
IF [Postal Area]>=2040 AND [Postal Area]<2060 THEN "R3"
ELSE
IF [Postal Area]>=2060 AND [Postal Area]<2080 THEN "R4"
ELSE
IF [Postal Area]>=2080 AND [Postal Area]<=2100 THEN "R5"
ELSE Null()
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF

 

The nested IFs produced the correct result.  Am I correct in assuming that SWITCH is unable to handle logical conditions in the Case clauses even though it doesn't return an error?  Is there something I'm missing?

 

 

courtlykumar
7 - Meteor

can anyone provide me data set for practice.

ChristineB
Alteryx Alumni (Retired)

@courtlykumar This article's attachment (Formula_Tool_Conditional_Statements.yxmd) has some data sets you can use to follow along with this article and practice.  Scroll down to the bottom of the article to find it!  

 

Hope that helps! 

subhajits11
8 - Asteroid

Thanks a lot for this informative article @Christine.

switch makes if-else easy.

subhajits11
8 - Asteroid

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.

Capturesd.PNG

 
 

This is possible by "find replace " ,but I don,t think that's the solution.

 

Regards,
subhajit

subhajits11
8 - Asteroid
tpostlewate
7 - Meteor

Quick question- The formula: IIF([Day] != 'Sat' OR [Day] != 'Sun', 'Weekday', 'Weekend') produces everything as weekday. Shouldn't Saturday and Sunday be weekend?
2016-08-01 Mon Weekday
2016-02-22 Mon Weekday
2016-03-12 Sat Weekday
2016-04-10 Sun Weekday
2016-05-06 Fri Weekday
2016-07-04 Mon Weekday
2016-06-05 Sun Weekday
I don't know enough about this to figure it out myself, but I wanted to at least ask.

dpuls
7 - Meteor

Your condition will always evaluate to TRUE.  Change OR to AND. 

Shelly3
5 - Atom

How can I change a part of a string? For instance, if there is an underscore, ex. "P1_2", I want to change the underscore to "W" so that it reads "P1W2".

NeilR
Alteryx Alumni (Retired)

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

 

NeilR_0-1616522371383.png

 

Biraj
7 - Meteor

Loved the switch function!!!

InPadure
5 - Atom

The IF formula for grouping by ages is right (according to the problem stated), but it it only works for integer age values.

If ages would have been available in date/time data type, it would have left people between 40 and 41 years old into the "Others" group.