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

Question - Data Validation

reginawhelan
8 - Asteroid

I've created a flow that's validates and cleanses manually inputted (Excel Spreadsheet) data so it can load into our system without and issues. 

 

I am stuck on the postal code. Since the data is manually inputted there are some with missing characters or invalid sequences. Our system requires the A1A 1A1 alphanumeric sequence. 

 

Is there a formula that would report on invalid sequences, for example: 1A1 A1A, A1A 1A, etc...?

4 REPLIES 4
AlteryxUserFL
11 - Bolide

This should be moved to the Designer Discussion,  but this workflow should solve your problem. It will compare input postal codes to all postal codes to check for a match. 

reginawhelan
8 - Asteroid

Thank you. We do multiple loads every day and this list will eventually be outdated. Do you know of a formula that would check the format of the postal code?

AlteryxUserFL
11 - Bolide

Try putting this into a formula tool and let me know if it works. 

 

Update: Add one more check at the end for non white space special symbols.  

 

 

 

IF Length([ZIP]) = 7 and  !regex_Match(Substring([ZIP],0,1),"\d+") and  regex_Match(Substring([ZIP],1,1),"\d+")  and  !regex_Match(Substring([ZIP],2,1),"\d+") and  regex_Match(Substring([ZIP],4,1),"\d+") and  !regex_Match(Substring([ZIP],5,1),"\d+")and  regex_Match(Substring([ZIP],6,1),"\d+") and Substring([ZIP],3,1) = " " and if REGEX_Match([ZIp], "[\w+|\s]+") then "TRUE" else "FALSE" endif 


then "Match" else "Not Match" endif 

 

 

reginawhelan
8 - Asteroid

Thank you! Works perfectly.

Labels