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

Need help parsing 1 field containing County names and Zips codes

Average_Badger
7 - Meteor

 

I need a way to parse out the values in a field that contains multiple county names on some lines, along with Zip codes.  I also need to match these county names and zip codes with their associated States and 'LOB_DESC' codes.  County names need to go on one file and zip codes on another.  I know there is a 'Text to Column' Tool and i was thinking of a way to use that.  

I included a sample shot of the data to get a better idea of what im talking about.

Any advice or ideas would be greatly appreciated.

 

Thanks!

 

 

 

 

 

 

Capture.PNG

7 REPLIES 7
patrick_digan
17 - Castor
17 - Castor

@Average_Badger I think the text to columns would be the right tool! I'm assuming that you always have counties list first, each one separated by a comma. If zips are listed as well, I'm assuming there's a : separating them from counties. I've attached a quick sample. Basically use the text to columns to split the counties and zips with the : delimiter into separate fields. Then use text to columns on both the county and zip field to split it into rows using the , delimiter.

 

Let me know if that doesn't do it!

Average_Badger
7 - Meteor

Thanks for the quick reply!  Yes, most of the values are separated by commas, but it also looks like there are some : in there between a county name and a zip code (EX: 'CLARK:89138, 89144,89143 etc')

These are Counties and their associated zip codes within those counties.  Perhaps i can do this in two steps and parse just the counties separated by commas, then deal with the zips and counties.

patrick_digan
17 - Castor
17 - Castor

I think my solution should handle both counties and zips like you're describing. It just wouldn't handle a situation where you have something like: Clark:12345,David:56789. I didn't see anything like that in your sample data you posted.

Average_Badger
7 - Meteor

Here is an example of what i was talking about.  These are mixed in with the counties separated by commas.

 

Capture2.PNG

patrick_digan
17 - Castor
17 - Castor

@Average_Badger Here is a picture of the workflow I attached:

 

Capture.PNG

 

The first text to columns tool is using : as a delimiter and splitting the data into 2 columns. So in your case, it would put Santa Barbara in one column and all the zips in the next column. Then I have 2 separate text to columns tools to handle the counties (the first column) and zips (the second column) where it's splitting them into rows using the , delimiter. So in your case all those zips would then get split into multiple rows. in the second text to columns.

Average_Badger
7 - Meteor

Fantastic!  I will try this out and see what i can do.  I appreciate it!

Average_Badger
7 - Meteor

This works great, thank you so much for help!

Labels