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 the data into being tall instead of wide, by selecting Record ID and Field 1 as key columns.
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 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 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).
We have strings with an uneven number of delimiters, parsed from the right.