As promised, I’ve decided to kick off a series of blog posts focusing on Alteryx as I go about learning how to use the tool most effectively for my data adventures in 2017.
Before I get into the technical details, I should back up a little and talk about ‘me and tech’ to help you understand the tone and content of these articles better. (if you don’t like detours to the 90’s, feel free to skip to the instructions further down)
Tentative first steps
Growing up, we’ve had a computer for as long as I can remember and I was lucky enough to start using the internet in 1995 at the tender age of 10 to research information on gerbils (by necessity: I ended up with a pair rather than brothers and needed to figure out a way to control the reproduction efforts they went to before the house was overrun by cute little brown mice).
I’ve always enjoyed the world that was opened up to me through the internet and using technology from an early age.
But no one ever taught me the really techie bits. I had many friends who worked as DBAs, network or system engineers and some even founded their own tech companies that are still going strong today. But I was more the ‘mascot’ of the club than a contributing member.
I tried to learn how to code but never went beyond ‘Hello World’. It just didn’t really appeal to me and reading heavy books with no clear idea of what I could do with the knowledge and skills afterwards.
This was around the year 2000 and the internet certainly wasn’t the same as the one we know and use today.
What this meant for me was that I never considered a career in IT or a degree in a tech subject. A bit of a regret to be honest, not at least giving it a try, but from where I stood it didn’t look like my kind of thing.
The downside is that for me using a new tool always causes a bit of anticipation.
For one, I don’t want to break anything. But more importantly, I’m worried that I’ll look stupid when I ask someone a question and don’t understand the answer. Sounds mad? Maybe, but to me a lot of the tech talk is like a foreign language and people assume I have certain knowledge which I don’t. So they use terms which make me just smile and nod while scratching my head and heading straight into a google search after our conversation.
Or someone tells me “just do A, then B, then E and you’ll get to the solution”. Hold on there! How do I do A? Why do I have to do B? And can you please tell me about steps C and D so I can actually get to E?
I have found a way to solve this problem: Find a friendly person who explains a process to me once from start to finish with all the detail before I go off and do it on my own. You see, I’m not dumb or slow. I just need a decent explanation once and then I can be left to my own devices. Usually. In high school I had really shoddy grades in maths. But not because I’m stupid, because once I got a private tutor who explained the material in a way that I understood, I managed to finish my secondary education career with a very decent 12 out of 15 possible points. Not bad after many years of suffering.
How this applies to Alteryx
The way I approached Alteryx was a bit similar. Minus the suffering.
At first when I tried to build a workflow, I looked in the online help and played around in the tool but couldn’t get anything to work. Yes, I hear you, it’s just ‘drag and drop’. But which of the gazillion tools am I meant to use? And what’s the tool configuration all about?
People say it’s very intuitive, but to me it certainly wasn’t. I’ll have to be honest there. I guess I was used to the simplicity and instant visual feedback I enjoyed in Tableau.
Alteryx was an enigma for me, so I closed it and rather disappointingly gave up for a few days.
Thankfully I had to use it for work, so there was no way around figuring it out. So I sat down with my colleague who’d been using it a while and got him to walk me through building my first workflow.
He pointed out all the essential configuration details, the tools I’d need for my specific task and let me play with it. And voila, I was able to successfully blend and transform some data and publish it to different types of output files. That first success was all I needed to continue with some more enthusiasm.
In one of my work projects I had to create a lot of Alteryx workflows to pick up data from many different sources and turn it into a consistent output for consumption by Tableau. That was fun and a good opportunity to get more practice.
Re-acquainting myself after a bit of a break
After a lengthy break (moving countries and changing jobs) during which I didn’t use Alteryx at all for several months, I am now back in the game.
In my day job I obviously deal with data a lot as well, so there’s a good chance that I’m using Alteryx regularly to blend, enhance and enrich datasets for analysis. But mostly my Alteryx practice comes from Makeover Monday. As I obtain datasets for Makeover Monday challenges, I transform them into .tde files at a minimum, but sometimes I need to do a bit of tidying up along the way, so I get a fair bit of practice doing that.
The second week of Makeover Monday was the first week in which I chose the dataset and it was a nice and simple one. I obtained the original data as an .xlsx file from Statista (add hyperlink) and then used Alteryx to turn it into a .tde as we usually publish both formats.
Yes, I could just open the .xls in Tableau and save it as a .tde but why not just get into the habit of using Alteryx for the transformation?
Using Alteryx to speed up my Makeover Monday data prep
In the next couple of paragraphs I will explain what I did in Alteryx and what the results were for each step.
To many this will seem super-basic, but there might be a couple of people just starting out with the tool and having similar reservations to those I held when it was all new to me.
Not to worry, I’ve got you covered ;-). I’ll promise to break everything down as far as possible and give you the ‘why’ as well to help you understand the reasons behind choosing certain tools and changing configurations.
I will assume no prior knowledge because, well, you know why… 😉
Using Alteryx to convert an Excel spreadsheet into a .tde file
First you’ll need an Excel spreadsheet you’d like to transform. Pick something nice and simple: a few columns and rows with no fancy formatting or so (remember we’re just getting started here…). Start with a new workflow in Alteryx. Your screen should look like this:
At the top of the screen you have a menu with all the tools you can use. For this workflow we will only use three of them.
On the left-hand side you can see the configuration menu where we will adjust the setting for each tool as required. The white space in the middle is your canvas where you’ll drag your tools. And finally there is the results window at the bottom of the screen. This is where you can see whether what you did worked out J.
Tools required for this workflow
In the workflow for my Makeover Monday data I needed only three tools:
- Input Data: this is where I choose the Excel file that contains my data
- Select: this is where I remove unnecessary fields from the dataset and change the data type of the fields I want as needed
- Output Data: this is where I tell Alteryx to create a .tde file of the data in a specific location
Let’s build it!
Step 1: Drag the Input Tool onto your canvas
Step 2: configure the Input Data Tool
Step 3: add the Select Tool
Step 4: configure the Select Tool
Step 5: add the Output Data Tool
Step 6: configure the Output Data Tool
Step 1: Drag the Input Tool onto your canvas
Step 2: Configure the Input Data Tool
We now have to configure the tool, which in this case simply means telling Alteryx where to pick up the Excel file from.
a) Click the downward button and select ‘File Browse’
b) Choose your Excel file from its location
c) Pick the sheet (if applicable) and click OK
d) Alteryx will give you a preview of the data in the configuration window on the left-hand side
e) Press the ‘Run Workflow’ button to see the data in your results window
f) You’ll be able to see the result below your workflow
Step 3: Drag the Select Tool onto your canvas and connect it to the Input Data tool
Step 4: Configure the Select Tool
We now have to configure the tool, which allows us to pick and choose which fields stay in the dataset as well as what data type those fields should be.
Go from this:
First I changed the year field to an Int16 to change it to a whole number rather than a decimal number. Then I unselected the fields F4 and F5 because they are emtpy and not required.
That’s it. Run the workflow again and see the results in the results window…
Stay with me, we’re almost done here.
Step 5: Add the Output Data Tool
The data has to go somewhere and that somewhere is going to be a .tde file. First we add the Output Data tool to our workflow:
Step 6: Configure the Output Data Tool
a) Let’s tell Alteryx where to save the .tde and all that stuff. This is similar to the input steps…
b) select a location for your file and give it a name…
c) choose your Output option: when you run the workflow, do you want to overwrite the data, append new data or create a new file?
d) Run your workflow and you’re done!
And that’s all there is to it. Not that tricky, right?
I obviously went into a LOT of detail here but if you’ve never used Alteryx before you may find this useful and next time we can skip over half the detail and just focus on the punchy things.
I am excited about my next Alteryx post because I have something really cool coming up that Chris Love helped me with, so I can’t wait to get cracking with those next workflows…