Last week we had our first experience with survey data, needless to say the Text to Columns tool was used many many times. This is great if the data you’re working with is not going to change, but what if your data updates and you have an additional answer to include?
Say, for example, you had the following data set:
You want the different answers for question 2 to all appear in their own columns, so use the Text to Columns tool splitting on the field ‘Q2. What type of TV do you watch? Tick all that apply.’, and you want 3 columns in the split, like so:
which is just want you want – great! But what if the response for ‘User ID’ 2 gets updated, such that the result for ‘Q2. What type of TV do you watch? Tick all that apply.’ includes News, as well as the original three answers (Comedy, Sport, Documentaries)? Your original workflow will not update to accommodate the change, and will return this:
So now the third column of answers for ‘Q2…’ has two answers in for the second row – which is not what you want! A nice trick to deal with this an make your workflow dynamic is to select the option for ‘split to rows’ in the Text to Columns tool configuration pane, this way Alteryx will add as many rows as needed and you won’t have to specify how many you want, which is subject to change. Let’s go back to the original data set, and use the ‘Split to Rows’ option:
Obviously this is still not the output you want, but we’re not quite finished yet. We can then use the Data Cleanse tool, the Multi-row Formula tool and the Cross Tab tool to achieve the final workflow:
I won’t go in to the details of the Data Cleanse tool or Multi-row Formula tool, but you can find more info here and here from a couple of my fellow data schoolers. And for information on the Cross Tab tool, look here. But just so you know, here are the configuration windows so you know how I used them:
So, after all this, we can run our workflow and finally get to where we want to be:
Now, I know what you’re thinking… Why go through all of this effort, just to get the same result as before? Well, if we update our answers for ‘User ID’ 2 to include ‘News’ again, here’s what happens:
we automatically get our additional column! So although it might seem quite a long process to have to go through to get the text in to columns, but it’s worth it for two main reasons:
- It will automatically update for any changes in the data set
- You don’t have to manually count the amount of columns you need if you have a big data set
And I’m sure you’ll agree it’s definitely worth the effort, just for these two reasons alone! Go try it for yourself!