Everybody uses IF statements in Excel. EVERYBODY. It is one the most popular decision making functions on the platform.

Fortunately in alteryx they are just as simple as in Excel.

So using the dataset outlined in the 1st blog of this series (with all our player rankings now filled in for both winners and losers, alongside their nationality and points).  What if we had a question along the lines of ‘did the highest ranked player win the match. Well in Excel we would just throw an IF forumla which asked whether the ‘Loser Rank’ was greater than the ‘Winner Rank’, if it is then return ‘HIGHER’, otherwise return ‘LOWER’.

Something like…

So now to recreate this in alteryx.

We need an INPUT tool to add our data to the workflow.

Secondly we need the FORMULA tool. Now I haven’t introduced the FORMULA tool yet in this series. But basically it allows you to write statements in a similar manor to Excel if you were to put an ‘=’ at the beginning of a cell. For an alteryx intro to the FORMULA tool then click here.

So how would our statement read? Well if you select the functions tab on our FORMULA tool then a menu with all functions appears, we can then find the formula we need. In this case it is under the ‘Conditional’ drop down.

So it says ‘IF c THEN t ELSE f ENDIF. But what does this actually mean? Well if we highlight the function it gives more detail in the bottom section (As i have done).

IF condition THEN True ELSE FALSE ENDIF

In other words, smaller letters represent aspects of the function that require our input.

But where are the commas and brackets!!

Well, the ENDIF acts as our closing bracket. The THEN and ELSE act as our commas. And the lead space between the IF and our Condition means alteryx knows to identify this as a function. In a similar manor, our THEN, ELSE and ENDIF statements must all have a space both before and after for alteryx to pick them up successfully.

So with this knowledge we can now complete the formula as in excel.

IF(F2>E2,”LOWER”,”HIGHER”) becomes IF [Loser Rank] > [Winner Rank] THEN ‘LOWER’ ELSE ‘HIGHER’ ENDIF

A quick note on field names may be neccessery here. In alteryx, If there are spaces in field names then you would require your field name to be wrapped by [] otherwise you can just simply use the field name with no square brackets.

This image shows the function used, bottom left, and our output (identifical to Excel), bottom right.

So this is how you write IF statements with alteryx.

Now again there are a few things to note:

1. Be wary of the ‘Type’ of your output field as this will affect your true and false conditions. I had mine set to ‘V_WString’ which allows text output. Others do not.
2. You can write multiple formulas in the same FORMULA tool. By typing or selecting a field name from the blank box below that of the one you are working on (in the top left of the configuration window).
3. Use single quotation marks when writing strings.
4. Yes you can write nested calculations, I know you were thinking this! How? Well as an example, what if I wanted to only return the ‘did higher or lower win?’ value for games where the losing player was French?

   Its simple, providing you can remember the syntax from before.

   IF condition THEN IF condition THEN true ELSE false ENDIF ELSE false ENDIF

   If you are writing this in the false section of the formula then we use ELSEIF instead of ELSE IF

   So: IF [Loser Nationality] = ‘FRA’ THEN IF [Loser Rank] > [Winner Rank] THEN ‘LOSER’ ELSE ‘WINNER’ ENDIF ELSE ” ENDIF

   I am yet to find a restriction on the number of nested IFS on alteryx, unlike Excel which is limited to 64 levels.

   

I hope this provides a good, clear explanation! There is one more method of IF statement that I haven’t introduced to you in this blog which is the IIF (If (bool is true) return x; else return y;). I have yet to find a use case for this function, but when I do, I will blog!

Next up will be COUNTIFS, my personal favourite Excel formula.

Ben

#VizLikeAnArtist