Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Get word before nth instance of pipe in pipe-delimited file


Hi All,


I need help in trying to give structure to an unstructured data set. So I had a bunch of excel files with unknown numbers of tabs that needed to be consolidated. Each tab had a table which exists randomly in the sheet. 


I was able to consolidate all of them and removed all the unnecessary rows and now I'm left with the rows that contain all the data that I need. (I consolidated all columns into one and separated them using the "|" character)


I'm currently stuck on how I'm going to do it in Alteryx such that all the headers are aligned. I already have a column that identifies the index of the header which the rows at the bottom should follow but I don't know how I'm going to parse it dynamically? RegEx would be best for this but I don't know if it's possible to specify an "instance" or "Index" variable in it.


I'm also looking at the specialized formula - GetVal(index, v0, v1), but can't figure out how to make it work.


My data is similar to below, where the "Text to Parse" column is my reference. Index is the number of "|"s before the word "Available" which the row below should also use in finding the word to parse


Text to Parse   Index Parsed Word  
Nov 12, 2019|Available|Almost Gone|Out of Stock|Spoiled|1Available
|Grape|Lemon|Boysenberry Large Variety|Satsuma|1Grape
A|Nov 12, 2019|Available|Almost Gone|Out of Stock|Spoiled|2Available
||Jujube|Watermelon|Blueberry|Date Extra Sweet|2Jujube
|A|Fruity Madness|Nov 12, 2019|Available|Almost Gone|Out of Stock|Spoiled|4Available



I'm using Alteryx Version 2019.3.5.17947


Thank you!




Alteryx Partner



This might not be the most efficient way to do it, but I actually don't know how to use the GetVal() formula myself!


I hope the attached does what you want - this was done quite quickly - adding screenshot below. By the way, the last row doesn't match because the index of 4 should actually be Nectarine (considering how all the other rows were done).


Hope this helps.





Text to rows.jpg




Thank you sososo much Tom! It's very smart!!

Alteryx Partner

Glad I could help!


Another option is to use the Tile tool to assign the "index" to each section of the Text to Parse








I know there are working solutions out there, but I wanted to include one more:

You can solve this with a regular expression.


Here's a function (add a new field using a formula) that will take in Text To Parse and an index and return the appropriate string.


REGEX_Replace([Text to Parse], '(?:[^\|]*\|){'+TOSTRING([   Index])+'}([^\|]*).*', '$1')


The '$1' returns the marked group, which is all non-pipe characters after the first N pipes - where N is defined by indexes.  So it basically replaces everything else in the string with the parsed value.


Hopefully this helps!  It should run a little faster than a text to columns approach. 


I'm amazed! Works perfect Claje. Thank you!!