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

[MECC]

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|	1	Available
|Grape|Lemon|Boysenberry Large Variety|Satsuma|	1	Grape
A|Nov 12, 2019|Available|Almost Gone|Out of Stock|Spoiled|	2	Available
||Jujube|Watermelon|Blueberry|Date Extra Sweet|	2	Jujube
|A|Fruity Madness|Nov 12, 2019|Available|Almost Gone|Out of Stock|Spoiled|	4	Available
|A||Honeydew|Nectarine|Guava|Grapefruit|	4	HoneyDew

I'm using Alteryx Version 2019.3.5.17947

Thank you!

Mariel

[TomWelgemoed · Accepted solution]

Hi,

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.

Best,

Tom

[MECC]

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

[TomWelgemoed]

Glad I could help!

[ChrisTX]

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

Chris

[Claje · Accepted solution]

Hi,

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. 

[MECC]

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