This short blog will show you how to parse uneven strings and also parse from the right in Alteryx. Both are surprisingly common things you’ll need to do, and both take the tiniest bit of brainpower that could be used for something else.
Parse Uneven Strings
Sometimes you have to parse strings, and sometimes those strings don’t have the same number of delimiters in each row. If you parse them like normal, you’ll end up with a whole bunch of nulls or risk have your data not parsed correctly. So what do you do?
TEXT INPUT TOOL
Your text could be anything.
TEXT TO COLUMNS TOOL
From the text input tool, try to figure out how many times you need to spit the strings. If you cant be bothered counting, just pick an excessively high number and split the columns that many times. In this case, I chose the \s (space) delimiter and chose 20 columns.
RECORD ID TOOL
The next step is to add a Record ID to the strings. This serves as both a reference, and a way to transpose the data/ group the strings later on.
TRANSPOSE TOOL
Transpose the data into being tall instead of wide, by selecting Record ID and Field 1 as key columns.
FILTER TOOL
Select a filter tool, and filter ‘Value’ on IsNotNull().
So what we did was parse some uneven strings and got rid of the extra columns that would otherwise have just filled up some extra rows with nulls. From here you can crosstab the data back to being wide, but you’ll end up with nulls again.
Parse from the Right
SORT TOOL
Sort the data by Record Id (Ascending) then Name (Descending). This reverses the order of the parsed strings, but keeps them in order by the Record ID.
SAMPLE TOOL
Sample the First N rows based on the number of times you wanted to split the data from the right. In this case I selected First 1 Rows.
Our goal was to parse the strings from the right instead of the left. And while this method is slightly clunkier than a typical text-to-columns tool, it does get the rightmost part of the string (which you ordinarily wouldn’t be able to do).
END RESULT
We have strings with an uneven number of delimiters, parsed from the right.
Author: Kevin Prescilla
As a late-stage PhD candidate, Kevin’s appreciation for data analytics grew during his studies into poultry nutrition, or as he calls it, “chickens”. It was this appreciation which spurred his decision to change career paths and ultimately led him to apply to the Data School. In his spare time he enjoys powerlifting – ever challenging himself to beat his last max weight - as well as all kinds of gaming, from board to PC. If Kevin could go anywhere in the world, where would it be and why? Well, the answer is Antarctica, as he is fascinated with how people can live and survive down there (although some might argue because it’s the furthest place you can go on Earth from a chicken).