IF you have no programming/advanced Excel experience and is starting to use Alteryx THEN read this ELSE you can skip this post

First time I heard about IF expressions was during a class of introduction to code (OK, I have never been an advanced Excel user, I have to admit). It was something completely new to me. My background is in journalism, my graduation and master’s degree are both in Communication, so simple things like IF functions impress me 😀

During the Data School, I am discovering that IF conditions are one of our best friends. Well, at least mine! Both Tableau and Alteryx allow you to use IF expressions – as a “Calculation” in Tableau and as a “Formula” in Alteryx – and IF is a very dynamic expression that you can use in different situations.

How does the IF function work?

I will explain it in this post in a way which isn’t technical,, so if you have a technical background, cover your ears (or eyes). Imagine that you are planning to run this Sunday. However, if you are like me, you would like to run only if it is a sunny day. If it is not sunny, you would like to do another thing, let’s say, go to the cinema.
So IF sunny Sunday THEN run ELSE go to the cinema would be the instruction to yourself, which means that if you wake up on Sunday morning and it is raining (which means that your condition to run is not true), you are giving up your plan to exercise and going to the cinema instead.

You can also have more than one condition per time. Back to my sunny weekend, I can decide that if sunny I am going to run, if it is cloudy I am going to cook, if it is raining I am going to the cinema and if anything else happens (there are so many options of bad weather in England!), I am staying at home.

So IF sunny Sunday THEN run in the park
ELSEIF cloudy Sunday THEN cook
ELSEIF raining Sunday THEN go to the cinema
ELSE stay at home.

When instructing Alteryx instead of your brain, you need to remember three things:

1. Always finish your IF expression with ENDIF. Otherwise you will receive an ugly red message saying you have a “malformed if statement”.
2. If you are in doubt about how to format the function, you can always click in “Functions” then “Conditionals” and check how to structure the data.
3. You can have as much ELSEIFs as you desire (well, if I discover a limit one day I let you know), however, you need to have an ELSE before the ENDIF.

You cannot create an expression with ELSEIF, ELSEIF, ELSEIF then ENDIF. If you try, you will find again the ugly red message saying you have a malformed if statement. I am saying it because today I got stuck trying to discover why my function wasn’t working until figure out that I was using only ELSEIFs! If you do that, Alteryx will not know what to do with the part of your data that doesn’t answer one of the set conditions.

That is the reason why insert a ELSE is important: it is saying to the software what to do with anything else it finds that doesn’t attend to any of the previous conditions. Back to my last example, either if it is snowing or if there is a hurricane (any temporal condition that I haven’t created a specific output), I am staying at home.

Just to finalise, I am sharing two different situations we used the IF expression today while preparing data.

FIRST: IF function to change data into rows

While preparing survey data, after splitting a column into rows and then merging it with similar data, we ended up with messy values in the rows.

We should have the values Q1, Q2, Q3, Q4, Q5 and Q6 in the same column, however, our Q6 values were messy. Instead of having it as Q6 we had 1, 2, 3 and 4.

An IF statement helped us to solve it identifying those numbers and changing them all to Q6. The statement bellow is saying: “Hi, Alteryx, if you find values in the column ‘Name’ that start with ‘Q’, THEN keep the value, ELSE change the value to ‘Q6’”.

SECOND: IF function to update null values

Another situation our beloved IF statement saved us today was while preparing a dataset formed by the availability of athletes by date. In other words, data showing when the athletes can play or when they are mothballed.

Which was the issue? We initially had only one column containing all the dates. When we split it into two, the periods that were not finished yet appeared as “null” values in the column. Instead of “nulls” we would like today’s date stated.

IF saved us again. In the following statement we are saying: “Hey, Alteryx, IF you find a ‘null’ value in the column ‘EndDate’ THEN print today’s date, ELSE keep the date you find.”

Now tell the truth: IF is a very cool statement, isn’t it? Feel free to add your favourite IF statement situation in the comments. It is always great to see other possible uses!