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

Separating rows from the right on specific character

David92
7 - Meteor

Hi there,

 

I am working with the following database and i am trying to separate the postcode from the rest of the the address. 

image.png

 

I know i could separate the columns by using 'London' as a delimiter and work from there.

 

From a skills development point of view I am wondering if it is possible to split it using a right trim using the second space, between London and the first letter of the postcode.  

 

Also not all the postcodes are the same length in the data set, so i cant split on a certain character. 

 

Any help would be appreciated greatly.

 

David

 

 

 

 

2 REPLIES 2
MarqueeCrew
20 - Arcturus
20 - Arcturus

@David92,

 

The moderators moved your post while I was writing to you.  The bottom line is that I prefer a Regular Expression that looks at the possible values and populates the postal code based upon known PC formats (wikipedia):

 

Validation[edit]

The format is as follows, where A signifies a letter and 9 a digit:

Format Coverage Example
AA9A 9AA WC postcode area; EC1–EC4, NW1W, SE1P, SW1 EC1A 1BB
A9A 9AA E1W, N1C, N1P W1A 0AX
A9 9AA B, E, G, L, M, N, S, W M1 1AE
A99 9AA B33 8TH
AA9 9AA All other postcodes CR2 6XH
AA99 9AA DN55 1PT

 

The code looks like this as a regular expression:

REGEX_Replace([Address], ".*(\w{2}\d\w\s\d\w{2}|\w\d\w\s\d\w{2}|\w\d\s\d\w{2}|\w{2}\d\s\d\w{2}|\w{2}\d{2}\s\d\w{2})", '$1')

The expression has 5 OR conditions, each separated by a pipe (|) character.

 

I have included that in the attached workflow.  Here is a picture of some alternative methods, each having it's own merits and cautions.

 

Capture.png

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
David92
7 - Meteor

@MarqueeCrew

 

Thanks for the help!  Appreciate it.

 

Best

 

David

Labels