This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Find answers, ask questions, and share expertise about Alteryx Designer.
General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!
We have a data feed which contains a free text field which members of the public can type comments into. The feed contains thousands of records. We need to remove any personally identifiable information (PII) from this field before it is loaded into a database table. This includes email addresses, phone numbers and UK postcodes. We have three regex expressions to identify these and replace the PII with a placeholder. The email and phone regexes are working fine.
However, the UK postcode regex throws some false positive matches which we don't want to replace. We also have a list of valid UK postcodes held in a database table. UK postcodes can have a few different formats and our regex is pretty good at identifying all these formats but inevitably, some false positives are cropping up.
We would like to
Iterate over any regex matches in the field and for every match:-
Lookup the regex match against the UK postcode table and if it is a valid UK postcode :-
Replace the regex match with the placeholder "**postcode removed**" in the input field.
For example, the input field could look like:
"V34ry happy that the news is now back on C4 9pm. Viewer in SW1P 2TX"
We want to change it to the following before loading into the destination table:
"V34ry happy that the news is now back on C4 9pm. Viewer in **postcode removed**"
N.B. "SW1P 2TX" is a valid UK postcode. "V34ry" (mistyped "very") and "C4 9pm" are false positives from our UK postcode regex but are not valid UK postcodes.
I'm new to Alteryx so am not sure how to achieve what we want here. Do I need to write a custom component to do this (which I am happy to do) or can this be achieved out of the box? Alternatively we could probably do this inside the DB with a stored procedure or similar but we'd prefer to perform the redaction before hitting the DB.
Instead of using a RegEx, what about using the Find Replace tool under the Join tab directly and feed the list of actual postal codes into the right side? This should minimize false positives. The only problem would occur if the zip code is not in its official format.
I have tested your approach by creating a macro which encapsulates the redaction functionality using Find & Replace. I was initially concerned that performance would not be great (the postcode table has 1m+ entries) but in fact, performance seems to be OK - the postcode table is read once and presumably cached locally for the duration.