Week 2 of Data School. It turns out Andy gets bored if things slow down so he decided this week we had to:

• Get introduced to Alteryx
• Prepare for the Tableau qualification exam that we will take next Monday
• Complete a project that required cleaning up data with Alteryx

This is how it went on my side:

• Monday: Alteryx is the coolest tool ever, think of all the things I can do with it
• Tuesday: I hate Alteryx, I cannot do anything with it, my career as a data analyst is ruined
• Wednesday: I cracked it! Watch me making Alteryx do what I want

On the serious side, here is what I have learned about Alteryx:

Browse is your friend.

Below is my workflow for this project. You cannot see much, but look at the amount of green binoculars. They show you how data look at every step. If you screw up at one step, you can keep looking back until it looks fine and then change your workflow until it does what it’s supposed to.

(Multi-rows / Multi-fields) Formulas are also your friend, albeit an annoying one.

You will yell at them a lot, but once you figure out the syntax you can shape and bend them the way you please. Here are a few of the things I did with them and you can do so much more.

• Remove fully empty rows. Just create a new field with an IF formula that shows a 0 if the row is completely empty, a 1 if it isn’t. Then filter it so that it shows only the 1 rows.
• Rename rows. Below is the formula I have used to rename non-empty rows with the value of the previous row

IF ISNULL([RowName])

THEN [Row-1:RowName]

ELSE [RowName]

ENDIF

• This is instead the formula in case you have more than one condition on which to rename the cells:

IF [RowName] =  “Variable 1” OR

[RowName] =  “Variable 2” OR [RowName] = “Variable 3”

THEN  [RowName]

ELSE [Row-1:RowName]

ENDIF

• Sum rows with null fields. I had a variable split in three different columns, some of which with null values. They cannot be added as they are, so a little tweaking is required:

IF ISNULL([RowName]) then 0 else [[RowName] ENDIF

+

IF ISNULL([RowName2]) THEN 0 ELSE [RowName2] ENDIF

+

IF ISNULL([RowName2]) THEN 0 ELSE [RowName2] ENDIF

Comment as much as you can.

You did it and you think you can remember it, but you can’t. Look at my workflow above, there are a lot of boxes, which contain either the formula or a description of what I did and / or was trying to achieve.

So here is the result of this week’s labour: https://goo.gl/iRHul0. It’s a viz about children in care in the UK. I wanted to add the Alteryx workflow, but it is too large to be seen clearly. Feel free to reach out to me and I will share it with you.