Conditional functions

Data Transformation in Alteryx

Josh Honken

Senior Associate at Knowesis

Course overview and welcome

data transformation icons

  • Chapter 1: Formula tool
  • Chapter 2: Transpose tool
  • Chapter 3: Cross Tab tool
Data Transformation in Alteryx

The Formula tool

A blue icon of the formula tool in Alteryx software. It contains a bubbling beaker like those in a scienctific laboratory.

  • Create a new column
  • Update a column
  • Perform calculations and operations

$$ $$ $$ You can use it to:

  • Apply conditional statements
  • Format dates
  • Apply mathematical calculations
Data Transformation in Alteryx

Functions and operators

Conditional Functions: test data against a condition

  • IF, ELSEIF
  • IIF

Logical Operators: combine statements and allow for increased complexity

  • AND (&&)
  • OR (||)
  • NOT (!)
Data Transformation in Alteryx

IF statements

  • Syntax: IF c THEN t ELSE f ENDIF
  • Returns t if the condition c is true, else returns f

United States map with the four states referenced lightly highlighted

Data Transformation in Alteryx

IF statements

  • Syntax: IF c THEN t ELSE f ENDIF
  • Returns t if the condition c is true, else returns f

$$

State Region
Texas
California
Indiana
New York

United States map with the four states referenced lightly highlighted

Data Transformation in Alteryx

IF statements

  • Syntax: IF c THEN t ELSE f ENDIF
  • Returns t if the condition c is true, else returns f
  • IF [State] = 'TX' THEN 'South' ELSE 'Not South' ENDIF $$
State Region
Texas South
California Not South
Indiana Not South
New York Not South

United States map with Texas highlighted

legend.png

Data Transformation in Alteryx

IIF Function

  • Returns x if bool is true, else returns y
  • Syntax: IIF(bool, x, y)
  • IIF([State] = 'TX', 'South', 'Not South'

$$

State Region
Texas South
California Not South
Indiana Not South
New York Not South

United States map with Texas highlighted

legend.png

Data Transformation in Alteryx

ELSEIF statements

  • Returns t if the first condition c is true
  • else returns t2 if second condition c2 is true
  • else returns f

IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF

IF [State] = 'TX' THEN 'South' ELSEIF [State] = 'CA' THEN 'West' ELSE 'Other' ENDIF

State Region
Texas South
California West
Indiana Other
New York Other

elseifmap.png

Data Transformation in Alteryx

Multiple conditions in an IF statement

  • AND (&&): true if both conditions are true
  • OR (||): true if either condition is true
  • NOT (!): true when condition is false

IF [State]='Texas' AND [City]='Dallas' OR [State]='Texas' AND [City]='Fort Worth' THEN 'Dallas Fort Worth' $$ ELSE 'No match' ENDIF

State City Dallas Texas?
Texas Dallas Dallas Fort Worth
Arkansas Dallas City matches but not state
Texas Fort Worth Dallas Fort Worth
California San Francisco No match
Data Transformation in Alteryx

CONTAINS()

  • CONTAINS(String, Target)
  • CONTAINS([Customer Name],'z')
Customer Name
Claire Gute
Sean O'Donnell
Pete Kriz
Zachary Taylor
Data Transformation in Alteryx

CONTAINS()

  • CONTAINS(String, Target)
  • CONTAINS([Customer Name],'z')
Customer Name Contains Z?
Claire Gute 0
Sean O'Donnell 0
Pete Kriz -1
Zachary Taylor -1
Data Transformation in Alteryx

Our dataset

The dataset for this course is: Genesis_LLC_Company_HR_Data.xlsx

Age Attrition BusinessTravel DailyRate
51 0 -1 684
52 0 0 699
42 0 0 532
47 0 0 359

logo for the mock company used throughout this course, Genesis research and development

Data Transformation in Alteryx

Let's practice!

Data Transformation in Alteryx

Preparing Video For Download...