Let’s talk Alteryx Copilot. Join the live AMA event to connect with the Alteryx team, ask questions, and hear how others are exploring what Copilot can do. Have Copilot questions? Ask here!
Start Free Trial

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
Top Solution Authors