Regex in Tableau Prep

by Kristine Wiesner

During our dashboard week last week, I was faced with a situation in data cleaning with Tableau Prep, where I have to separate a field of number codes individually so  I can match these codes to another file.  

A little data backgrounder, this data is about the UK Gender Pay Gap and I was interested to find out what are the gaps in each industries.  Standard Industrial Classification (or SIC code) are description of a company's nature of business.  

In the dataset,  a company may have or can have several SIC Codes or in other words, can be classified into different industry.  That's why there are multiple codes in one field.  For simplicity's sake, I just wanted to extract the first SIC code to have a general idea of the nature of business of the company.  

There are multiple ways to approach it, of course.  It was quickly done with Alteryx but I would like to try to solve it with Tableau Prep.  I made a post in Convo for help and Owen Barnes jumped in a call with me.

This was solved in two ways, the long way and short way.  Long way are series of regex calculated fields, until the end goal was reached.  

The short was just one super calculated field.  

Regex is also one of Tableau's additional functions, which I have not really used until now.  Here are some basic definitions:

REGEXP_REPLACE(string, pattern, replacement) - returns a copy of the given string where the regular expression pattern is replaced by the replacement string

REGEXP_MATCH(string, pattern) - returns true if a substring of the specified string matches the regular expression pattern

REGEXP_EXTRACT(string, pattern) - returns the portion of the string that matches the regular expression pattern

REGEXP_EXTRACT_NTH(string, pattern, index) - returns the portion of the string that matches the regular expression pattern

Owen shared this useful tool called, Regex 101, its a site where you can debug Regex in real time, meaning you can see how the expression affect the data.

Im so grateful to learn something new about Regex from Owen and that it can also work in Tableau Prep!    Thanks Owen, you're a great instructor :)