Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

EDI Records - Parse one string of records into individual records

kheuer
8 - Asteroid

Hello,

 

I am trying to analyze EDI records and cannot figure out how to properly modify the Test To Columns tool to get what I need. See attached workflow for example record.

 

Current State:

All EDI Records are in one single field (it is one day's worth of records)

 

Desired State:

Every time the characters "LIN" appear in the field I want to add a line break, so that ultimately I have one record for each LIN (which should give me ~3000 individual rows).

 

Please help!

--- Kristina

4 REPLIES 4
DavidP
17 - Castor
17 - Castor

You can use a formula tool to replace "LIN" with something like a pipe delimiter and then use a text to columns tool to split to rows.

DavidP
17 - Castor
17 - Castor

replace([string_field],'LIN','|')

Thableaus
17 - Castor
17 - Castor

Hi @kheuer 

 

I did it in two ways, see attached.

 

REGEX_Formula.PNG

 

- Using Formula Tool + Text to Columns

In your formula, use this expression - REGEX_Replace([EDI], "LIN", "\n")

Then, Text To Columns to split to rows using "\n" delimiter

 

Or you could use the REGEX tool with Tokenize Method.

 

(.*?)(?:LIN|$)

 

See WF attached.

 

Cheers,

kheuer
8 - Asteroid

Awesome! That worked. Thank you so much.

Labels