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

Seperating irregular data

jrysdon
5 - Atom

Hello,

 

I am trying to separate data from one field with a long string in each row into separate fields. My data is taken from a txt file and looks like this:

 

10/02/19                  SGR OP                   193                   1
09/12/17                  SP ADL                    1889                  2
01/23/19                  CHG ROG                3566                 186                                         Room RG.204/AIST
06/16/18                  ALD SE RK              1655                 1                                              Forwarded from OS1657888

 

I am able to separate out the text into different fields using the text to columns tool and using " " as my delimiter but I haven't found a way to separate out values while keeping the spaces between data like "ALD SE RK" or "Room RG.204/AIST" which I need keep together in one field.

 

I am new to Alteryx and I apologize if this is hard to understand. Any help would be appreciated.

 

 

 

 

5 REPLIES 5
JulioMO
9 - Comet

Hi @jrysdon 

 

The workflow I would use to accomplish what you post is the following: 

Capture.JPG

 

With the "data cleansing" tool, I eliminate the leading and trailing spaces and the tabs:

Capture1.JPG

 

Then, I use the RegEx tool, as follows: 

Capture2.JPG

The output is the following: 

Capture3.JPG

 

Attached you will find the workflow. 

 

Regards!

estherb47
15 - Aurora
15 - Aurora

Hi @jrysdon

You can separate based on patterns using RegEx in Alteryx. Because the consistent delimiter is multiple spaces, I'd replace those with another character (in the example here, I've chosen a hyphen), then use Text to Columns to separate on the hyphen.

With a formula tool, the expression Regex_Replace([Field],"\s\s+","-") will replace anything that's 2 spaces or more with a hyphen.

image.png

 

Let me know if that helps!

 

Cheers,

Esther

jrysdon
5 - Atom

Thank you Julio, this has really helped.

 

Do you know how I would go about filtering out all the rows that start with a date?

 

Thanks

 

estherb47
15 - Aurora
15 - Aurora

Hi @jrysdon 

 

The date column, as parsed, isn't being treated as a date. You could use the Date Time tool to convert it into a date, if you'd want.

 

To filter on this column, I'd use a custom filter as follows:

 

Regex_CountMatches([Date field],,"\/")=2

 

That will filter where that column has 2 forward slashes within the field.

 

Cheers!

Esther

JulioMO
9 - Comet

Hi @jrysdon 

 

Did you mean filtering by a specific date, or simply filtering the rows having a date? If you meant the latter, @estherb47  gave you a pretty good solution!

 

Regards

Labels