I was recently inspired by some really great tile-maps that have been created in the Tableau community (e.g., see beautiful work by Chimdi Nwosu and Michael Dunphy). Thus, you know I had to come up with a way to construct a simplified map in this style with some data and share with my followers. In these two videos, I’m going to walk you through how to prepare the necessary data file in Tableau Prep Builder and then we’ll build out the tile-map in the second video, step by step.
This is a good intermediate level portfolio project for you to follow along with in order to increase your Tableau Desktop and Tableau Prep skills. We’ll use CDC data, specifically United States COVID-19 Cases and Deaths by State over Time, to build the tile-map.
The advantage of a tile-map is that it represents geographic regions (like states) at equal sizes. Thus, the distortions and biases introduced by differences in sizes are eliminated. In the case of the United States, data for smaller regions like Washington D.C. can be interpreted on equal footing with data for a much larger region like California.
Tableau Prep Builder helps to greatly simply the data shaping process. My only wish is that Tableau would integrate Prep into Tableau Desktop for one seamless data tool to rule them all, but I digress. The process below illustrates how simple it is take some data from an input file, and subsequently clean and pivot the data into a new file. Watch the first video, to learn how to build out this simple flow in Tableau Prep. If you do not have a copy of Tableau Prep, you can complete this lesson on a 14 day trial license of the tool, which you can download here.
Watch the second video for the step by step instructions to build out the tile-map above.
In this video I will teach you how to perform a multiple keyword search in Tableau. I’ll show you how to perform both an OR search & an AND search against keywords you type into your dashboard parameter.
In order to accomplish these tasks, we need to use regular expressions. As I mention in the video, regular expressions can be indecipherable, akin to hieroglyphics or something out of a Dan Brown novel to the average person. You can go find this information on the Tableau knowledge base, but I want you to be the person that understands WHY something works as opposed to just copying and pasting a solution that you cannot explain to yourself or others.
The use case for multiple keyword search is for those times when you need to filter your data by a comment, description or other free form blocks of text that are not necessarily subject to the most stringent data governance rules, because let’s face it, these types of fields exist in abundance. In the video I create a dashboard that illustrates the power that this type of search can bring to your dashboard.
You can interact with the dashboard from the video on my Tableau Public page.
OR SEARCH
1. CREATE PARAMETER
Create a parameter named Search Terms.
Data type: String
Current value: keep it blank.
Allowable values: All
2. CREATE CALCULATED FIELD 1
Create a calculated field named Regex String (OR) including the following formula:
"(" + REPLACE([Search Terms], ',' ,'|') + ")"
Let’s breakdown what is going on with the apparent gibberish seen above (there is a method to the madness here). I’ll assume you want to perform a search against a field that contains the terms “paper” OR “Paper”; the case of the spelling is a factor in the results (case sensitive). To adhere to proper regex formatting, you could write a valid expression as such:
(paper|Paper)
This pattern indicates that you want to return a match on the characters “paper” OR “Paper” literally. You can test this out at https://regex101.com/
Although for some reason the official Tableau knowledge base article says to use the REGEX_REPLACE formula in lieu of REGEX, it is not needed since we are doing a simple replace and not using a regex formula to aid our replacements.
In the Tableau calculated field above, we are using the REPLACE function against the parameter named “[Search Terms]” to replace every instance of a comma with the the ‘|’ pipe symbol.
Think about it, if you type the terms “paper, Paper” into the a dashboard’s search term parameter as seen below, the calculated field will reformat your list into the proper regex format by replacing the comma with a pipe and then enclosing the term within parenthesis; i.e., (paper|Paper).
3. CREATE CALCULATED FIELD 2
Next create a calculated field named Regex OR Filter that contains the following formula:
REGEXP_MATCH([Product Name],[Regex String (OR)])
Let’s breakdown what is going on with the formula seen above. The REGEXP_MATCH formula is evaluating a properly formatted regex expression which is contained within the [Regex String (OR)] calculated field we created in step 2. This calculated field will contain the formatted expression (paper|Paper).
REGEXP_MATCH returns TRUE if a substring of the specified string matches the regex pattern. In our case
[Regex String (OR)] = (paper|Paper)
Thus the REGEXP_MATCH function will evaluate the regex expression (paper|Paper) from the second argument against the [Product Name] field in the first argument. The function will return either TRUE or FALSE depending upon whether a match is located within this field.
3. DRAG FIELDS TO ROWS AND FILTERS SHELF
Place [Product Name] to rows and [Regex OR Filter] to filters, then select True.
AND SEARCH
In order to create the AND search for your dashboard, you will follow very similar steps to the OR search. 1. Create the same parameter named Search Terms as in the OR Search above.
2. CREATE CALCULATED FIELD 1
Next create a calculated field named Regex AND Filter including the following formula:
This may look like something out of a Dan Brown novel, but don’t let it intimidate you. In a similar fashion to the OR search where we replaced commas with a pipe symbol, we are simply replacing commas with the following characters between the double quotes “)(?=.*”
The proper regex pattern to indicate an AND search against the terms paper and Paper within a body of text would be as such:
(?=.paper)(?=.Paper).*
Knowing the proper regex format should make the contents of the calculated field above more clear. We are using the REPLACE function against the parameter named [Search Terms] to replace every instance of a comma with the the “)(?=.*” symbols. Thus “Paper, paper” values typed into the [Search Terms] parameter becomes reformatted into(?=.paper)(?=.Paper).*
Placing .* at the end of the string means that any combination of characters can be placed after the search terms and still yield a TRUE result.
3. CREATE CALCULATED FIELD 2
Next create a calculated field named Regex AND Filter including the following formula:
REGEXP_MATCH([Product Name],[Regex String (AND)])
4.DRAG FIELDS TO ROWS AND FILTERS SHELF
Then place [Product Name] to rows and [Regex OR Filter] to filters, then select True.
CONCLUSION
That wasn’t so horrible was it? If you’re a visual learner like me, then go ahead and watch the video to hammer home the concepts. Hopefully this post will help you understand how these regex patterns work and how they can add value to your next dashboard design! As always if you learned something drop a comment on the YouTube video and let me know.
Smoak Signals | Data Analytics Blog 