Overview
You can download a trial version of Alteryx here.
You can find the Excel source data here.
You can download the completed Alteryx module from here.
Understanding the workflow
Here’s the Alteryx workflow that will get the data in the format we want.

Figure 9 — Alteryx workflow for getting the survey data in an optimal format for analysis in Tableau
If this is the first time you’ve seen an Alteryx workflow it may look a little complicated (shades of Rube Goldberg) but think about how complicated the flow and decision tree is for what you do when you get up in the morning and get ready for work – it’s way more complicated.
Let’s start by focusing on the three data sources that we need (labeled 1, 2, and 3 in the diagram above).
- Data with numeric responses (we’ll use this for our demographic data, too)
- Data with text responses
- The Question Helper file (contains our meta data).
A note about SPSS files
For this example we’re using Excel data but the process would be identical for survey data saved in an SPSS format (.SAV file). SPSS encodes both the text and numeric data in a single file, but you need to input values from that same data source twice – once for text and once for numeric values.
When you specify an SPSS file as a data source you’ll see the following check box option.
Selecting “Expand Factors” will bring in the data as labels. Unchecking this box will bring in the data as numbers.
Demographics
Clicking the Select tool reveals the following settings.
A translation into English would be “open this Excel file and select these fields”. Here we keep RespID, Gender, Location, Generation, and Weight.
Numeric values
Using the same data source, selecting the Transpose tool reveals the following settings.
Here we indicate that we want to keep RespID and pivot all the other checked fields. Notice that the demographic components are not checked.
Looking back to the workflow diagram, we next follow this pivot with a Select tool which we use to rename the pivoted fields as shown here.
Here we are naming the first pivoted field Question ID and the second pivoted field Numeric value.
Dealing with nulls
While not critical, Alteryx allows us to eliminate null values before they come into Tableau. Here’s the setting for this tool.
Joining the demographic with the transposed (pivoted) numeric data
We’re now ready to join the demographic data with the pivoted numeric survey data. We do this with Alteryx’s Join tool. Here are the settings.

Figure 19 — Joining demographics and numeric data using the common field, RespID. Notice that we don’t include the redundant version of RespID that is coming from the right table.
Checking our progress
If we add a Browse tool after the Join we’ll see the following results.
Note that we could, in fact, get by with using just this in Tableau, but the whole point is to make things faster and easier, so we’re going to add text responses and the “Helper” data.
Text Values
This is identical to the numeric values except that we specify a different data source (one with label responses instead of numeric) and we rename the second pivoted field differently, as shown here.
Here we are naming the first pivoted field Question ID and the second Text value.
Tying Demographic, Numeric and Text Values Together
We need to combine the demographic / numeric values with the text values. We will again use Alteryx’s Join tool, but we’ll need to join on both RespID and Question ID.
The setting for the Join is shown below.
Adding the question meta data and outputting the results
We’re almost done; we just need to add the information that tells us about each Question ID so we can map the ID to its human-readable form, group related IDs together, and so on.
The setting for this Join is shown below.
The only thing left to do is output this to a new data source. In the sample workflow I output to an Excel file but you can just as easily write directly to a Tableau Data Extract file.
But Wait! There’s More!
I intentionally used data that was well behaved, but what happens if the data is “messy”? For example, suppose there’s an extra row below the field names? Or suppose that for a check-all-that-apply question, instead of 1s and 0s we instead have 1s and blanks (which would mess things up in Tableau)? Alteryx handles both of these scenarios (and dozens more) easily.
Is there a Downside?
If there’s any downside it would be the cost as Alteryx starts at around $4,000 per year. If all you are doing is some occasional survey data reshaping then this fee would probably be prohibitively expensive. If, however, you need to corral a lot of data – or if you need advanced data blending, geospatial analysis, or predictive modeling – then Alteryx is a huge bargain.
Hi Steve,
Thanks so much for this post!
I’ve been mostly successful following this post but seem to run into a problem trying to use the null filtering. Whenever I filter for nulls, it ends up reducing my case size by quite a bit– I have several demographic variables that have nulls and some that are complete for every single case.
Have you come across this problem at all?
Rachel,
I don’t think I would remove the null for demographics, I would just remove them for questions.
And even then, you may not want to do that, depending on how your data is set up. For example, for check-all-that-apply questions Qualtrics records responses as 1s and blanks instead of 1s and 0s, so deleting the nulls could be a big problem. See https://www.datarevelations.com/checkallcoding.html
Steve
Hey Steve, I use SPSS, and I was able to create SPSS macros that does exactly what Alteryx does, removing the need for Alteryx altogether!
Nick,
Indeed, there is some formidable scripting capabilities in SPSS and I know people who have gotten the data “just so” just using SPSS. Would you be able to share what you did?
There are other good reasons for using Alteryx for the data prep, but it is a pricey tool.
Steve
Hey Nick,
Could you send me the SPSS macros too?
Thanks in advance!
Evelien
Sure, I can certainly send you the syntax on Monday.
-Nick
May also share the syntax with me? Need to re shape SPSS data a lot.
Idham,
I asked Nick Z to write a guest blog post on the subject as I am not at all versed in SPSS scripting syntax.
Steve
Hi Nick,
May I ask if its not burden, can I have the syntax?
Idahm, I sent you an e-mail. Sorry we have not addressed this on the website yet.
Hope to get you something soon.
I have a similar process with far too much manual intervention. Would be great to see how its done in SPSS. Please email syntax if shareable.
Working on making this available.
[…] recently I recommended two different approaches. You can either use Alteryx and have a rock solid, robust, fully-featured, (and expensive) solution or you could have a […]
Hi! Great posts on survey data – really helpful and very easy to follow.
One question fro you: your data seems to already have a weight, however the survey providers I used don’t do this and is something we have to calculate manually ourselves.
What, in your opinion, is the best way to weight survey data (can this be done in Alteryx?) or best practice using other software packages?
Thank you
Rachel
Rachel,
This is a great question. What I have seen people do is prep their data using SPSS to add the weighting variable, then take that data into Alteryx, Easy Moprh, etc., and get it “just so.” I am not up to speed with Alteryx’s stat functionality but I would be very surprised if you could not do *everything* right inside Alteryx.
Steve
Hi Steve – thanks. I am going to explore this and will get back on the forum once I have found out whether Alteryx is fit for purpose for this.
Thanks for your responsiveness & great forum.
Rachel
Thanks so much for this! At the moment, I am working with survey data that has several matrices-type questions. When following your example above, I don’t want to pivot all of the data (except for demographics) because then I am unable to call out specific questions from my data set in Tableau. I do not have Tableau prep, and, since Tableau will not allow you to pivot multiple “check all” or matrix-style questions within one workbook, I find myself adding in multiple worksheets which is not ideal. Especially when you want to crosstab or compare a “select-one” question with several of the “check all” or “matrix”. Any tips on how to pivot my several questions in Alteryx so that I won’t need to input several worksheets containing one pivot each into Tableau? Thank you!
Jennifer,
Two possibilites besides Tableau Prpe.
You may be able to find and install the Tableau add-in to Excel. That is what I used to do (see the 2014 Tableau Conference presentation on my survey data page).
Or… you can download the trial version of EasyMorph. I think it allows you to do 30 transformations for free and that may be more then enough for what you have in mind.
See https://www.datarevelations.com/resources/easymorph/
Just wanted to let you know the Alteryx download file provides a 404 error
Thank you. I will try to address this.