Alteryx designer Discussions

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

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

Highlighted
Atom

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
|A||Honeydew|Nectarine|Guava|Grapefruit|4HoneyDew

 

 

I'm using Alteryx Version 2019.3.5.17947

 

Thank you!

 

Mariel

 

Highlighted
Alteryx Partner

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

 

Text to rows.jpg

 

 

Highlighted
Atom

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

Highlighted
Alteryx Partner

Glad I could help!

Highlighted
Quasar

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

 

Capture.PNG

 

Chris

Highlighted
Magnetar

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. 

Highlighted
Atom

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

Labels