community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Seperating irregular data

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.

 

 

 

 

Meteor

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!

Highlighted
Pulsar

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

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

 

Pulsar

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

Meteor

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