1. Conditional functions
Welcome to Data Transformation in Alteryx!
My name is Josh Honken and together we will learn how to transform your data. Let's get started.
2. Course overview and welcome
Alteryx is an ETL tool that lets you make some of the most complex data transformations approachable. Chapter 1 of this course covers the formula tool and logical operators. Chapter 2 covers the Transpose tool. Chapter 3 will cover the Cross tab tool. This video gets us started with the formula tool, conditional statements, and logical operators. Let's jump in!
3. The Formula tool
The Formula tool is used to create or update a column, and uses one or more expressions to perform calculations and operations.
There are many things you can do with the formula tool such as applying conditional statements, formatting dates, and applying mathematical calculations. For this video however, we will focus on conditional statements.
4. Functions and operators
Conditional functions and logical operators are key to enriching, summarizing, and reshaping data. Conditional Functions perform an action or calculation based on a test of data using an IF statement. The most common ones are IF, ELSEIF and IIF.
Logical Operators help to combine statements and evaluate a larger, more complex expression whose output is `true` or `false`.
The three operators are AND, OR, and NOT. Each keyword has a corresponding symbol. However, let's start by diving into the IF statement.
5. IF statements
If statements are both common and valuable. You'll use them a lot. Here is the syntax: Here the "c" stands for condition, the "t" for the result if TRUE, and the "f" for the result if FALSE. Don't forget ENDIF to close out the statement! If we wanted a different flag, this formula could be changed to show 1 and 0, yes and no, or any other preferred output.
6. IF statements
Now let's have a look at an example. Imagine we have a table with states, and we want a new column called region.
7. IF statements
If the state is equal to Texas then we want it to say South, otherwise it should say Not South.
There we go! We've successfully created a region column.
8. IIF Function
The IIF statement is essentially a condensed IF statement. Rather than having words between the elements, it uses commas. It takes a boolean test, output if true, and output if false.
9. ELSEIF statements
In the case of our ELSEIF example, we can create more than two outputs, as we have TX, CA, and all others. We can add as many ELSEIF conditions as are needed for our use case.
10. Multiple conditions in an IF statement
Here is an example of using logical operators for a complex IF statement. Because Dallas and Fort Worth are often referenced together, we are creating a custom group here. The first section checks for the state to be Texas and the City to be Dallas. If that is not true, the next section will also accept if the state is Texas and the city is Fort Worth. If neither of these two conditions are true, it will return 'No match' as the result.
11. CONTAINS()
How many customers have the letter Z in their name? We can use a formula to flag them, and then count with a summarize tool! We'll name the formula 'Contains Z?' and then start with the CONTAINS() function. The syntax is shown here. For our purposes, it will end up looking like this. The function is case insensitive, so we can just type a lowercase z in quotes.
12. CONTAINS()
We can see the case doesn't matter in rows 3 and 4, and we can see the output for a boolean Alteryx column is 0 for false, and -1 for true.
13. Our dataset
You will function as an HR Analyst, cleaning and shaping the data, as well as gathering business metrics and statistics. It has many fields about the employees of Genesis. The table shown is just a few of the many available columns.
14. Let's practice!
In the practice exercise, you'll determine the results for each row based on a complex conditional formula.