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

Design logic for matching UK Postal Codes

Lucasvital
7 - Meteor

Hi,

 

I have a list of persons in UK identified by a range of postal codes: 

PersonPostal Code LowPostal Code High
Person ABR0 0AABR9 9ZZ
Person BDA0 0AADA9 9ZZ
Person ZSW1A 0AASW1Z 9ZZ

 

I want match that specific Person to specific opportunities IDs based on Postal Code, but my other file has a specific Postal code that would follow in the range between Low and High

 

OpportunitiesZip Code
Opportunity YSW1E 5DH
Opportunity ZEC1Y 0RR

 

Output would like:

OpportunitiesZip CodePerson
Opportunity YSW1E 5DHPerson Z
Opportunity ZEC1Y 0RRPerson X

 

Anyone have any idea/suggestion on how to solve?

 

thanks so much

7 REPLIES 7
BrandonB
Alteryx
Alteryx

Is it always the 0 to 9 for the range? If so, I'm wondering if you could parse out the first three characters from the postal code and then just have

 

BR0

BR1

BR2

...

...

BR9

 

Then parse out the same in the opportunities and join on these characters. That way all that you would have to do is Generate Rows for each two digit postal code range so that they go 0 to 9

Lucasvital
7 - Meteor

Not really, there are cases that the first set of digits is 2 or 4 instead of 3, below is the whole list of potential postal codes:

 

Postal Code LowPostal Code High
BR0 0AABR9 9ZZ
DA0 0AADA9 9ZZ
EC0A 0AAEC9Z 9ZZ
E0 0AAE9 9ZZ
IG0 0AAIG9 9ZZ
RM0 0AARM9 9ZZ
SE0 0AASE9 9ZZ
SW0 0AASW9 9ZZ
WC0A 0AAWC9Z 9ZZ
W0 0AAW9 9ZZ
SM0 0AASM9 9ZZ
EN0 0AAEN9 9ZZ
HA0 0AAHA9 9ZZ
NW0 0AANW9 9ZZ
N0 0AAN9 9ZZ
WD0 0AAWD9 9ZZ
BS0 0AABS9 9ZZ
CR0 0AACR9 9ZZ
UB0 0AAUB9 9ZZ
BS10 0AABS99 9ZZ
CR10 0AACR99 9ZZ
EN10 0AAEN99 9ZZ
NW10 0AANW99 9ZZ
NW1W 0AANW1W 9ZZ
N10 0AAN99 9ZZ
WD10 0AAWD99 9ZZ
UB10 0AAUB99 9ZZ
SM10 0AASM99 9ZZ
DA10 0AADA99 9ZZ
E10 0AAE99 9ZZ
IG10 0AAIG99 9ZZ
RM10 0AARM99 9ZZ
SE10 0AASE9Z 9ZZ
SE1P 0AASE1P 9ZZ
SW10 0AASW9Z 9ZZ
SW1A 0AASW1Z 9ZZ
W10 0AAW99 9ZZ
N1C 0AAN1C 9ZZ
N1P 0AAN1P 9ZZ
AL0 0AAAL9 9ZZ
B0 0AAB9 9ZZ
CB0 0AACB9 9ZZ
CM0 0AACM9 9ZZ
CO0 0AACO9 9ZZ
CV0 0AACV9 9ZZ
DY0 0AADY9 9ZZ
GL0 0AAGL9 9ZZ
HR0 0AAHR9 9ZZ
IP0 0AAIP9 9ZZ
LE0 0AALE9 9ZZ
LU0 0AALU9 9ZZ
MK0 0AAMK9 9ZZ
NN0 0AANN9 9ZZ
NR0 0AANR9 9ZZ
PE0 0AAPE9 9ZZ
SS0 0AASS9 9ZZ
SG0 0AASG9 9ZZ
TF0 0AATF9 9ZZ
WS0 0AAWS9 9ZZ
WV0 0AAWV9 9ZZ
WR0 0AAWR9 9ZZ
DE0 0AADE9 9ZZ
LN0 0AALN9 9ZZ
NG0 0AANG9 9ZZ
ST0 0AAST9 9ZZ
LD0 0AALD9 9ZZ
LL0 0AALL9 9ZZ
NP0 0AANP9 9ZZ
SA0 0AASA9 9ZZ
SY0 0AASY9 9ZZ
B10 0AAB99 9ZZ
CB10 0AACB99 9ZZ
CM10 0AACM99 9ZZ
CO10 0AACO99 9ZZ
CV10 0AACV99 9ZZ
DE10 0AADE99 9ZZ
DY10 0AADY99 9ZZ
GL10 0AAGL99 9ZZ
IP10 0AAIP99 9ZZ
LE10 0AALE99 9ZZ
LN10 0AALN99 9ZZ
LU10 0AALU99 9ZZ
MK10 0AAMK99 9ZZ
NN10 0AANN99 9ZZ
NR10 0AANR99 9ZZ
NG10 0AANG99 9ZZ
PE10 0AAPE99 9ZZ
SS10 0AASS99 9ZZ
AL10 0AAAL99 9ZZ
SG10 0AASG99 9ZZ
ST10 0AAST99 9ZZ
TF10 0AATF99 9ZZ
WS10 0AAWS99 9ZZ
WV10 0AAWV99 9ZZ
WR10 0AAWR99 9ZZ
LL10 0AALL99 9ZZ
NP10 0AANP99 9ZZ
SY10 0AASY99 9ZZ
SA10 0AASA99 9ZZ
BB0 0AABB9 9ZZ
BD0 0AABD9 9ZZ
BL0 0AABL9 9ZZ
CA0 0AACA9 9ZZ
CH0 0AACH9 9ZZ
CW0 0AACW9 9ZZ
FY0 0AAFY9 9ZZ
DL0 0AADL9 9ZZ
DN0 0AADN9 9ZZ
DH0 0AADH9 9ZZ
HX0 0AAHX9 9ZZ
HG0 0AAHG9 9ZZ
HD0 0AAHD9 9ZZ
IM0 0AAIM9 9ZZ
HU0 0AAHU9 9ZZ
TS0 0AATS9 9ZZ
LA0 0AALA9 9ZZ
LS0 0AALS9 9ZZ
L0 0AAL9 9ZZ
M0 0AAM9 9ZZ
NE0 0AANE9 9ZZ
OL0 0AAOL9 9ZZ
PR0 0AAPR9 9ZZ
S0 0AAS9 9ZZ
SK0 0AASK9 9ZZ
SR0 0AASR9 9ZZ
WF0 0AAWF9 9ZZ
WA0 0AAWA9 9ZZ
WF0 0AAWN9 9ZZ
YO0 0AAYO9 9ZZ
BB10 0AABB99 9ZZ
BL10 0AABL99 9ZZ
BD10 0AABD99 9ZZ
CA10 0AACA99 9ZZ
CH10 0AACH99 9ZZ
CW10 0AACW99 9ZZ
DL10 0AADL99 9ZZ
DN10 0AADN99 9ZZ
DH10 0AADH99 9ZZ
HU10 0AAHU99 9ZZ
IM10 0AAIM99 9ZZ
LA10 0AALA99 9ZZ
LS10 0AALS99 9ZZ
L10 0AAL99 9ZZ
M10 0AAM99 9ZZ
NE10 0AANE99 9ZZ
OL10 0AAOL99 9ZZ
PR10 0AAPR99 9ZZ
S10 0AAS99 9ZZ
SK10 0AASK99 9ZZ
TS10 0AATS99 9ZZ
WF10 0AAWF99 9ZZ
WA10 0AAWA99 9ZZ
YO10 0AAYO99 9ZZ
HP0 0AAHP9 9ZZ
OX0 0AAOX9 9ZZ
BA0 0AABA9 9ZZ
BH0 0AABH9 9ZZ
BN0 0AABN9 9ZZ
CT0 0AACT9 9ZZ
CF0 0AACF9 9ZZ
DT0 0AADT9 9ZZ
EX0 0AAEX9 9ZZ
GY0 0AAGY9 9ZZ
GU0 0AAGU9 9ZZ
JE0 0AAJE9 9ZZ
PL0 0AAPL9 9ZZ
PO0 0AAPO9 9ZZ
RH0 0AARH9 9ZZ
ME0 0AAME9 9ZZ
SP0 0AASP9 9ZZ
SL0 0AASL9 9ZZ
SO0 0AASO9 9ZZ
SN0 0AASN9 9ZZ
TA0 0AATA9 9ZZ
TN0 0AATN9 9ZZ
TQ0 0AATQ9 9ZZ
TR0 0AATR9 9ZZ
GX0 0AAGX9 9ZZ
RG0 0AARG9 9ZZ
KT0 0AAKT9 9ZZ
TW0 0AATW9 9ZZ
BA10 0AABA99 9ZZ
HP10 0AAHP99 9ZZ
OX10 0AAOX99 9ZZ
BH10 0AABH99 9ZZ
CT10 0AACT99 9ZZ
CF10 0AACF99 9ZZ
DT10 0AADT99 9ZZ
EX10 0AAEX99 9ZZ
GY10 0AAGY99 9ZZ
GU10 0AAGU99 9ZZ
KT10 0AAKT99 9ZZ
PL10 0AAPL99 9ZZ
PO10 0AAPO99 9ZZ
RG10 0AARG99 9ZZ
RH10 0AARH99 9ZZ
ME10 0AAME99 9ZZ
SP10 0AASP99 9ZZ
SL10 0AASL99 9ZZ
SO10 0AASO99 9ZZ
SN10 0AASN99 9ZZ
TA10 0AATA99 9ZZ
TN10 0AATN99 9ZZ
TQ10 0AATQ99 9ZZ
TR10 0AATR99 9ZZ
TW10 0AATW99 9ZZ
GX10 0AAGX99 9ZZ
BT0 0AABT9 9ZZ
BT10 0AABT99 9ZZ
AB0 0AAAB9 9ZZ
DD0 0AADD9 9ZZ
DG0 0AADG9 9ZZ
EH0 0AAEH9 9ZZ
FK0 0AAFK9 9ZZ
G0 0AAG9 9ZZ
IV0 0AAIV9 9ZZ
KA0 0AAKA9 9ZZ
KY0 0AAKY9 9ZZ
ML0 0AAML9 9ZZ
PA0 0AAPA9 9ZZ
PH0 0AAPH9 9ZZ
TD0 0AATD9 9ZZ
ZE0 0AAZE3 9ZZ
KW0 0AAKW9 9ZZ
HS0 0AAHS9 9ZZ
AB10 0AAAB99 9ZZ
DD10 0AADD99 9ZZ
DG10 0AADG99 9ZZ
EH10 0AAEH99 9ZZ
FK10 0AAFK99 9ZZ
G10 0AAG99 9ZZ
HS10 0AAHS99 9ZZ
IV10 0AAIV99 9ZZ
KA10 0AAKA99 9ZZ
KW10 0AAKW99 9ZZ
KY10 0AAKY99 9ZZ
ML10 0AAML99 9ZZ
PA10 0AAPA99 9ZZ
PH10 0AAPH99 9ZZ
TD10 0AATD99 9ZZ
BrandonB
Alteryx
Alteryx

This outlines the approach that I was looking to use. I don't work with UK Postal Codes very often so there are probably other criteria that are important. Maybe this is a start?

 

UK Postal Codes.png

BrandonB
Alteryx
Alteryx

Generally converting a range to a list of lookup values uses generate rows in a similar fashion to my previous comment so I'm not sure if you just need the rules to be a bit more flexible based on the possible combinations. 

JoeS
Alteryx
Alteryx

Nice work @BrandonB  for having a go with UK postcodes from over the pond 😉

 

From looking at your problem, it looks like you can just use the Postcode area? AKA the first and/or second letter as the matching criteria?

 

Not sure if that's due to you posting a more simple scenario though?

 

To do that you can create the area in a new field using the RegEx tool:

 

Workflow.png

 

Edit:

I have just seen you split out after 9 in the district, so not as straight forward as matching on postcode area. I'll reply back with a working solution....

 

 

Lucasvital
7 - Meteor

Hi @JoeS .

 

Wanted to follow up if you found another alternative and can share the working solution?

 

Thanks

JoeS
Alteryx
Alteryx

Hi @Lucasvital 

 

Sorry I got distracted with our Inspire Conference.

 

Please find attached a workflow that builds out all the possible districts and keeps the relevant low and high.

 

You should then be able to match to the district column and can ignore the incode (last 3 characters).

 

Workflow2.png

Labels