Get word before nth instance of pipe in pipe-delimited file
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you sososo much Tom! It's very smart!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Glad I could help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I'm amazed! Works perfect Claje. Thank you!!
