1. String functions and elseif with the formula tool
Hello! As we jump into using the formula tool, I want to point out that I will be using the Superstore dataset. You will be using a different HR dataset for your exercises.
Let’s get started with the formula tool. In this case, I’m going to do a few operations with the formula tool that you might encounter as well. We can find the formula tool in the preparation palette.
I’ll drop a formula tool in place on the canvas.
Before we get into conditional functions, let's explore a string function available in the formula tool, such as split by delimiter. I’m going to do a split of the customer ID and get everything after the dash. I’m going to add a column instead of overwriting it, and I’ll call it Customer ID number.
Next, I’m going to type GETRIGHT, and you’ll see a bunch of options come up with GET and what I’m able to do. In this case, I’m going to get the right part of the string.
We need to type what string we are getting this from, which is the field customer ID and then we need to type our delimiter and put it inside quotes for it to work. We’ll see the data preview populates the first row, and we can check our result as 12520. That is what we’re hoping for, so that’s a quick way to get a split done.
Another way to do this would be using the Text-To-Columns parse tool. If we chose the hyphen as a delimiter, we can easily split the column in two. There are often multiple ways to do the same thing in Alteryx!
A quick tip for a cleaner workflow is to do a stacked formula inside the same tool. We can do as many as we need without adding another icon to the canvas.
If we were to get a requirement from our manager to rank sales as High if they meet a certain threshold, we could do that with an IF statement. Let’s make one! We will name this column 'Sales Grade Basic' in the output column dropdown, and then type into the expression editor: IF Sales is greater than 1000 THEN ‘High’ ELSE NULL() ENDIF. ENDIF is different than END. Don’t forget to type ENDIF with no spaces, or you’ll get an error! We can see something weird is going on, because we aren’t getting the color coded syntax and the formula isn’t working. The reason for that is that we are trying to perform a numeric operation on a text field. We need to make sure Sales is a numeric field before we do this. Let’s drag a Select tool in front of our Formula tool, and fix it first. We’ll change Sales to ‘Double’, which is a numeric data type that works well for currency, and then we can jump back to the Formula tool.
This will work correctly now - and it will mark all records that have Sales over 1000 as 'High' in our new column, and leave all others blank.
Let’s expand on this and make additional conditions:
IF [Sales] > 1000 THEN 'High'
ELSEIF [Sales] > 500 THEN 'Medium'
ELSEIF [Sales] <= 500 THEN 'Low'
ELSE NULL() ENDIF
Now we have all records marked with High, Medium, or Low sales.
This should give you a feel of how even the most complex conditional statements can start with small building blocks of IF THEN ELSE ENDIF. We can see in our results window that both of our formulas are working! That’s it for now - next we’ll move to an exercise where you’ll be doing some data cleanup using the formula tool.
2. Let's practice!