The Tableau Desktop Certified Associate Certification Exam requires you to follow the below rules when taking the test. For questions, email certification@tableau.com or visit tableau.com/certification.

• You need to be alone in a room with no other person present. Before starting the exam the proctor will make sure of this by asking you to rotate your webcam a full circle to get a complete view of the room.

• You need to present a Government-issued Photo ID to the proctor by holding it in front of the webcam.

• Your computer or laptop device must be connected to a power source. Your webcam, microphone, and speaker must remain powered on during the entire duration of the test.

• You may use an external monitor only if – a) The laptop display settings show only a single screen is in use. b) The laptop is closed. c) A webcam is provided from the external monitor. d) An external keyboard and mouse are added.

• Your desk must be clear of miscellaneous items. Else the proctor will ask you justification for each item and why you need it.

• You must be having Google Chrome browser and a stable connection speed. Your proctor will guide you on steps to check your webcam, internet speed, and browser compatibility test. The recommended internet speed is 4 Mbps for download and 2 Mbps for upload.

• Your phones or headphones should not be in the exam area during the duration of the test.

• Your proctor should be able to clearly see you via webcam during the entire duration of the test.

• Your Task Manager should show only Google Chrome and there should be no other running processes. The Proctor will verify this before beginning the exam.

• Your laptop should have Automatic Software Updates disabled. If on a corporate network, ensure there is no connection impact due to the firewall by connecting with your IT department.

Colors are an integral part of our daily life. They also play a very important role in our communication without us realizing the number of times we are processing the message that colors are communicating. Looking at the color of the sky, your mind tells you the approximate time it is, a blacked-out TV communicates that it is off, if you see everyone dressed in Red, chances are there, that it might be Valentine’s day. 

Just like our lives, we want our reports also to be colorful. The popularity of using colors is based on the fact that the human brain processes image 60,000 times faster than text, and 90 percent of information transmitted to the brain is visual. This is the reason you have Red, Green, and Orange colors on Traffic Signals instead of words like 'Go', 'Stop' or 'About to Stop'. Imagine, how difficult it would be for people to comprehend the words while riding their vehicles. The use of colors has made the same task so easy. Using colors in your reports too can have multiple benefits. You will be evaluated on such concepts in the Tableau Desktop Certified Associate Certification Exam. They communicate your analysis with the user effectively and make your report look appealing at the same time. So, we are convinced that colors are an effective medium to communicate your message and draw the attention of the users to the areas that are important. Now, let's see how to use this medium effectively in our Tableau reports.

1. Sequential Color: Use single sequential colors, when you want to focus on density of the metric. For example, Sales for a product can be very high to very low. Lighter shade signifies the lower range of values and darker shades signifies the higher range of values. The use of single color can be used to help to get the user’s focus on areas of high density. When used in maps, it can help identify regions that can be grouped together based on the performance of a metric. Using single color metric will attract the user’s attention to darker shades while lighter shades tend to get ignored. Hence, single color shades should be avoided when the contrast is very high, and it is important to highlight both highest and lowest vales of the metric. 

2. Diverging Colors: This can be used when we want to communicate a metric with high contrast. For example, profit can be very high, or it can go in the negative (which can also be called a loss). Very high profit and very high loss are equally important points for further exploration. Finding answers using this approach can be helpful in the Tableau Desktop Certified Associate Certification Exam. For such cases, using diverging colors can be used to highlight the areas that need user’s attention. Diverging colors can be used for metrics like profit, profit ratio, customer satisfaction index, etc.

3. Categorical: These can be used when we want to group the results in various categories For example, categorize the Sales in 'Above Target' or 'Below Target' Category. In such cases, we can specify the color code for each category and display the color legend for user to understand the meaning of the colors used. Avoid the use of multiple colors of same shades which can confuse the user. The colors with contrast are ideal to use here.

Categorical colors can also be used to individual comparison of dimension values.

Here also, we would choose contrasting colors to be able to differentiate. This is just like our traffic signal example, where a category has a color assigned. 

4. Highlight: Highlight technique is used when we want a value to stand out in a cluster. These can be useful in scenarios where department head is interested in knowing where it’s department stands in comparison with other departments. In such cases, we would use a specific color (like orange) for the specific department and another color for all other departments (like blue). Highlight technique helps the user to immediately focus on the area of his interest. The user will not have to search for his area of interest in a cluster or values. 

5. Color Opacity: Opacity is the transparency of color. Opacity can be set in range of 0% to 100%. 

Color opacity will have an impact where we have overlapping marks. Ideal example for this would be a scatter plot. In scatter plot with high level of granularity, we have multiple marks overlapping each other. 

6. Border for Color Marks: You can also add border to the color marks. Borders help to clearly demarcate the areas for any given value. This can be especially useful when you have multiple marks of similar shades adjacent to each other. In absence of borders, a user may not be able to get the clear picture of start and end points of any mark.

7. Maintain Design Standards: It is a good practice to keep the color codes consistent in your reports. While designing a report, color codes to be used should be noted in a notepad, excel workbook or a design document, if you have one. This will help maintain consistency in your reports. For getting the color code, double-click the color in Edit Colors window. The color code can be in the format of Red-Green-Blue codes, Hue-Sat-Val codes or HTML codes.

8. Consider Color Vision Deficiency: Color Blindness or Color Vision Deficiency(CVD) is the decreased ability to see color or differences in color. It is more common than we would generally think of. As per portal colorblindness.org, ‘Worldwide, there are approximately 300 million people with color blindness, almost the same number of people as the entire population of the USA!’. The number is compelling enough to consider the scenario while designing our reports. There are two major types of Color Blindness. Some people face difficulty distinguishing between red and green, while others face difficulty distinguishing between blue and yellow. If you know your target audience, you may want to ask them, whether they would like the reports to be designed addressing such needs. To make it easy, Tableau provides a color palette for Color Blind. 

If you have audience with CVD, then it is recommended to use Color Blindness Simulators (available online) to view how your dashboards will look to your audience. If you are using colors to communicate the analysis results, then the simulators will help you to know whether the colors used are communicating exactly what you want.

9. Color coding for Future values: There are scenarios where you are working on mocked-up data for the development of your report & you might not get all the scenarios to color-code your values. For example, you have a calculated field that groups the customers in ‘Silver’, ‘Gold’, and ‘Diamond’ based on the Sales for that customer. Below is the calculated field that you created to categorize the customers:

When you created your visualization, you noticed that there are 2 categories as the there are no customers falling in ‘Gold’ category. 

This is because, you are working on mocked up data provided to you and you don’t have write access to the data to accommodate all scenarios. However, you know that in when your report will connect to actual data, it will have customers falling under ‘Gold’ category. Whenever Tableau gets a new value, it assigns a default color code which might not be aligned to your design requirements. As there is no data for ‘Gold’ category in the current data, you will not be able to assign your required color code for ‘Gold’ category. Good news is that, you don’t have to wait for actual data and assign a color for ‘Gold’ to set your report right for actual data in advance. Let’s see how we can fix this with a workaround and make your report Future Proof. 

Temporarily change your calculated field so that at least 1 record has ‘Gold’ as a result. As we will go back to original calculation, it doesn’t matter whether the calculation is technically correct or not. The only thing important here is to somehow get ‘Gold’ as one of the results so that we can assign a color code. Below is the sample change in calculated field. 

After making changes in the calculated field, you will see that now your data has a ‘Gold’ category and you can assign it a color code of your choice. Once a code is assigned to the category, it will be saved in your Tableau workbook. Even if you go back to your original calculation, the code assigned to the ‘Gold’ category will be preserved in your Tableau workbook. In the future, when your report will be connected to actual data and it will have customers falling in the ‘Gold’ category, the chart will have the correct color code that you assigned. Make sure to improvise and derive your answers with such tricks during the Tableau Desktop Certified Associate Certification Exam.

Thus, Data Visualization is an art of representing numbers. Tableau has provided a feature-rich functionality for adding colors to your reports, which, when utilized effectively, can take your reports to next level.

Overview

Let's explore few combined practical examples of concatenation, conversion, analysis and extraction of texts that can help you understand string calculations even better. Such understanding can be immensely beneficial during the Tableau Desktop Certified Associate Certification Exam. Since Tableau offer a brief explanation of every function directly in the Calculated Field editor under ‘String’ we won’t go into details regarding their definition. Proficiency is a result of practice.

Preface of String Calculations

The term string calculations can somehow sound a bit confusing since calculations on texts sound irrelevant and contradictory.

So of course, we don’t really use string calculations for performing calculations on strings. What we really use string calculations for is manipulating with strings and analyzing them. That being said, with the help of string calculations we are able to concatenate text, change text (for example, to upper, lower or proper case), convert the text or parts of it to number and dates, extract subtexts or just analyzing them ( for example, how many characters they contain), etc.

However, Tableau is maybe not the best option for manipulating your string data since these calculations are the ones that are the most heavy for one view and creating a lot of them in one dataset in Tableau can be overwhelming for the view. But still, Tableau works as a powerful tool for testing out these types of calculations before permanently applying them to your dataset by using another way, for example SQL.

If you are new to Tableau and want to learn more about string calculations and how you can create them in Tableau, be sure to practice our Tableau Desktop Certified Associate Practice Exams. This article, though, presents your next step into the world of string calculations. This article is an insightful practical guide that will lead you through some of the most commonly used string calculations. If you are looking for a certain string calculation at the moment, this article may offer a solution in your case. But even if you are not looking, we recommend you taking a brief look inside and upgrading your ‘Tableau game’.

For the sake of simplicity, we would group the examples in four groups based on the goal that should be achieved with the calculation - Concatenation and Insertion, Conversion and Manipulation, Analysis and Extraction.

Concatenation and Insertion

The concept of concatenation applies in cases when multiple fields are able to be combined together with a goal to serve a certain context-based purpose. That being said, string fields are combined together in order to create another united string that will have its own individual meaning. How we use concatenation in Tableau is considered to be different than in any other programming language. In Tableau, due to analytical reasons, concatenate generally refers to cases when two or more fields are combined together for a particular reason – to form another field that will mean something by itself and can be used afterward for more in depth analysis. Or to make things easier for us to analyze. This combined field will have a meaning that can be connected to the meaning of the fields that created it or it can have different meaning of value as a result. These techniques come handy during various questions of the Tableau Desktop Certified Associate Certification Exam.

Tableau in general is a visual analytics tool that put heavy emphasis and effort on employing visualizations in order to smooth the path of insight derivation. Even though Tableau is not a programming tool, functionalities such as concatenation are available. Concatenation as a feature is very useful in various contexts. By using concatenation, we are able to make a field more readable, to combine two or more field into one and cut the extra work, to make an analysis easier or just add something extra to the fields that can improve the overall design of the view.

1.       Concatenate strings together

Explanation: Concatenate two or more string values. To clarify, tie up two dimensions together instead of being presented as two separate columns.

Example: Concatenate [FirstName], [MiddleName] (if applicable) and [LastName] to be presented as one string.

[FirstName] + " " +IfNULL([MiddleName] + ". ","") + [LastName]

How does it work?

With the help of the plus operator (+), the existing three dimensions are concatenated together. Since having a middle name does not come by default as the first and last name, that is why we have IFNULL function ahead of it. What IFNULL actually does is check if middle name exists. If so, the spaces together with the middle name are inserted between the [FirstName] and [LastName] fields. If not, just space is inserted between these two fields.

2.       Concatenate strings and date

Explanation: Concatenate more string values, but this time together with a date or just a part of a certain date. In order to be more clear, you may want to add some additional hard coded text to it so you know why the date is used.

Example: Concatenate [FirstName], [MiddleName] (if applicable), [LastName] and plus, the month of birth as one string.

[FirstName] + " " +IFNULL([MiddleName] + ". ","") + [LastName] +" born in " + STR(Month([Birthday]))

How does it work?

Regarding the first part of the formula, it is the same one as the previous case. Afterwards, a hard coded text “born in” is added as a further explanation for the date that will follow. And at the end, we have the actual date which is converted to a string from the dimension Birthday. We do this with the help of the function STR that converts it into a string and MONTH that will take into account only the month part of the certain date.

3.       Insert line feed

Explanation: Concatenate two or more string values with an added line feed in between. By line feed we mean moving one of the strings in the following line.

Example: Concatenate [FirstName] and [LastName] to one string, but insert a line feed in between. That being said, the first name should appear in the first line, and the last name in the following line under.

[FirstName] + CHAR(10) + [LastName]

How does it work?

By using CHAR(10), we insert a line feed. The ASCII code number of the line feed character is 10. Also, you can directly insert a line feed in the Calculated field by doing this:

[FirstName] + "
" + [LastName]

4.       Insert quotation marks inside of strings

Explanation: Concatenate two or more strings and insert quotation marks inside the one string that comes as a result.

Example: Concatenate the [FirstName] field and the [LastName] field and add the initials in brackets and quotation marks at the end.

[FirstName] + " " + [LastName] + " ("" + LEFT([FirstName],1) + LEFT([LastName],1) + "")"

How does it work?

First of all, in order to create quotation marsk inside a hardcoded string, we need to use double quotation marks. Afterwards, we are using the LEFT function for extracting part of the string, creating a substring starting from left. First we state the accurate field as an expression, and then we add 1 which means that only the first character from left will be extracted.

5.       Ordinal numbers

Explanation: Transform a number into a string by converting it to ordinal numbering format. Ordinal numbers are numbers that express degree, quality, or position in a series.

Example: Convert the members of the field [No.] as ordinal numbers, for example 1=1^st, 2=2^nd , 3=3^rd etc.

STR([No.]) + IF RIGHT(STR([No.]),2) = "11" OR RIGHT(STR([No.]),2) = "12" OR RIGHT(STR([No.]),2) = "13" THEN "th" ELSE CASE RIGHT(STR([No.]),1) WHEN "1" THEN "st" WHEN "2" THEN "nd" WHEN "3" THEN "rd" ELSE "th" END END

How does it work?

First of all, the STR function converts the number into a string. Afterwards, we are stating several conditions with the IF clause and a CASE statement. First of all, by using the RIGHT function, we are making a condition depending on the last two characters of the string. If the last two characters are 11,12 or 13, the accurate extension is “th”. Then, by using the ELSE statement we are making place for the second condition. In all other cases, it really depends on the last character of the string. If it is 1 then the right extension is “st”, if 2 then “nd”, if 3=”rd”, and all others with “th”.

Conversion and Manipulation

The process of converting data from one to another format is known as a data conversion. Even though the concept of data conversion may seem simple, data conversion can present a really complex, but yet a critical step in the process of data integration or migration. We refer to the word critical because data conversion and manipulation enables the data to be readable, altered and executed in a process or database other than the one in which it was created in a first place.

For instance, if your business is using different techniques for storing the data ( especially when numeric values ) or if your business needs to establish a communication between countries or networks or users who view data in different character sets, you definitely are in a high demand of data conversion.

The main goal of data conversion is to prevent unwanted situations like data loss or corruption. Data conversion is responsible for maintaining the integrity of the data, create an information from a data or just to keep it readable.

For some data conversion cases, Tableau offers a specified formula that can convert the data in a wanted format very easily. As an example, only by using the UPPER function we are able to convert lower case field into an upper case. On the other hand, in some cases data conversion in Tableau may be a little more complicated. It may require solid understanding of the formulas that Tableau offers for a manipulation with strings. And more importantly, how they can be used together in order to perform the data conversion efficiently.

6.       One letter upper case

Explanation: Extracting the first character of a certain string and making It upper case. Please note that by combining this function with some other substring functions like LEFT, RIGHT, MID etc, you can specify the characters that you want extracted and then, make them all upper case.

Example: Extract the first letter from the value of the [FirstName] field and make it upper case.

UPPER(LEFT([FirstName],1))

How does it work?

First of all, the LEFT function states that the first character from the left should be extracted. Once the accurate character is extracted, it can be transformed from lower case to upper case by using the UPPER function.

7.       String to Date

Explanation: Convert a certain string to an accurate date format. Some dimensions when dropped to the Data Pane of Tableau, can be recognized as strings by default, even though they present date.

Example: Transform the field [OrderDate] that has a format “YYYYMMDD” in its relevant date format. One member of this field is presented as 20201109, which should be presented as 09/11/2020.

DATEPARSE("yyyyMMdd",[OrderDate])

How does it work?

One important thing that we should remember while using the DATEPARSE function, is that stating what part of the string is the year, which part is the month and which part is the day as well is crucial. Otherwise, Tableau may have difficulties recognizing on its own and the value won’t be transformed in an accurate way. That is why we are using the format “yyyyMMdd” In the formula. This way we are stating that the first four characters are the year, the following two are the month and the other two, the day.

8.       Date string

Explanation: Now the opposite case than the previous one. Convert a certain date into a string in a format that we define before hand (other than the default format of the date field). For some storytelling charts, the dates presented as text are more suitable than its default date format.

Example: Convert the date [OrderDate] into a string in the format “Month Day, Year”. For example, “November 9, 2020”.

DATENAME('month',[OrderDate]) + " " + STR(DAY([OrderDate]))+ ", " + STR(YEAR([OrderDate]))

How does it work?

The function DATENAME returns the name of the month of [OrderDate] as a string. Please note, that when using this function you should put the part of the date in quotation marks so Tableau will be able to recognize it. Afterwards, by using the STR function, you convert the day and the year as strings. Here you should only state the part of the date with upper case.

9.       Text bins

Explanation: Depending on the first character of a certain string, you can assign strings to different bins. Simply said, you can group strings by their first character in different categories.

Example: According to the first character of the string, assign the field [LastName] to different bins, “A-K”, “L-Q” and “R-Z”. Bins are a better solution than simple grouping on Tableau, since they are dynamic. If a new member from the field is added, it will automatically be added.

IF UPPER(LEFT([LastName],1)) <= "K" THEN "A-K" ELSEIF UPPER(LEFT([LastName],1)) <= "Q" THEN "L-Q" ELSE "R-Z" END

How does it work?

With the LEFT function we are extracting the first character on left, that will be crucial indicator for which bin the last name will be categorized in. With the UPPER function, the first character is converted to upper case. By stating “<= K” in the IF clause, we are making a condition that every letter that is K or before K, should go into the “A-K” bin. Every letter that is Q, or before Q belongs to the “L-Q” bin. Everything else is “R-Z” bin. You can adjust the characters and the texts based on your needs.

10.   Reverse 2 words

Explanation:  Change the order of two words in a certain string.

Example: Reverse the order of the first name and last name in the field [Customer Name]. In this field, the first name and the last name are separated by comma, for example “Stevens, Michal”. Our goal is to reverse the order of the words and turn it into “Michal Stevens”.

MID([Customer Name], FIND([Customer Name],",")+2) + " " + LEFT([Customer Name],FIND([Customer Name],",")-1)

How does it work?

First of all, we get the first name by using the function MID. We put the field as an initial expression first, and then we extract a substring starting 2 characters after the position of the comma. We have detected the comma by FIND. The parameter [length] of the MID function is optional. Afterwards, all the characters from the beginning of the string up to the position of the comma present the last name. That is why we are using the LEFT function and also, detecting the comma with the FIND function. This time we add -1 because we don’t want the comma to be returned. Note that between the first name and last name we add hardcoded “ ” that will be previewed as space between.

11.   Imperial to metric

Explanation: Convert a string that present size which is in imperial notation, into a number in the metric system format. For example,

Example: Convert a string with a size displayed in imperial notation into a number in metric system format, e.g. 5’6’’ converted to 170.69 cm.

INT( LEFT(REPLACE([FeetInches]," ",""), FIND(REPLACE([FeetInches]," ",""),"'")-1))* 30.48+ INT(MID(REPLACE([FeetInches]," ",""), FIND(REPLACE([FeetInches]," ",""),"'")+1, FIND(REPLACE([FeetInches]," ",""),CHAR(34))- 1- FIND(REPLACE([FeetInches]," ",""),"'"))) *2.54

How does it work?

By using the LEFT statement, we are able to extract the characters that are left to the first apostrophe. That is actually, the foot part. We are able to detect the apostrophe by using the FIND function. The substring from the FIND statement is used as a string in the LEFT statement. We add -1  To remove all the blanks as well, we use the REPLACE statement. After we get the extraction right, we are using the INT function to convert the string to a number. Once we have a relevant number, we do the multiplication by 30.48 which is actually the right number that converts foot to centimeter.

Regarding the next part of the formula, we use the MID function to extract everything from right to the first quotation mark and left to the first of the double quotation mark. We use the (CHAR (34)) since 34 is the ASCII code for double quotation mark. This part presents the inches. Again, the INTC function converts the string to a number and by multiplying this by 2.54, we can easily convert the inches into centimeter.

Analysis

The process of investigating, cleansing, modifying and modeling data with a main aim of discovering useful information, forming conclusions or supporting the decision-making cycle is known as data analysis. Data analysis as a process may support multiple approaches, diverse techniques and it is definitely used in many different businesses, branches or domains. Data analysis helps businesses to operate more effectively. The Tableau Desktop Certified Associate Certification Exam assesses on various key areas discussed below.

Concatenation and Insertion, Conversion and Manipulation and Extraction of data mostly refer to data integration, data mining or just cleansing data. After the data is cleaned, readable and modified to our needs, we are ready to take the next step - data analysis. By analyzing our data, we are actually making sense of it.

Since Tableau is mostly known for its analytics features, the possibilities how you can analyze your string data in Tableau is almost limitless and definitely something worth exploring.

12.   Count blanks

Explanation: Get the exact number of blanks in a certain string.

Example: From the dimension [Description], count the number of blanks that appear.

LEN([Description])-LEN(REPLACE([Description]," ",""))

How does it work?

With the help of the function LEN, we are able to measure the length of a string. That being said, LEN([Description]) gives us the length of the original string. On the other hand, REPLACE function removes the blanks in a string and create a temporary string without blanks. So, in order to count the blanks in a string, we should reduce the length of the temporary string with no blanks from the length of the original string.

13.   Count non blanks

Explanation: Count the number of actual characters in a string, without paying attention to blanks.

Example: Get the number of non-blank characters in the dimension [Description].

LEN(REPLACE([Description]," ",""))

How does it work?

If we are interested in getting the number of non-blank characters in a string, we should focus on the second part of the formula in the previous case. REPLACE will temporarily create a string that has no blanks in it, and by using the LEN function we are able to get its actual length.

14.   Count words

Explanation: Get the exact number of words in a string.

Example: Count how many words there are in the dimension [Description]

LEN(TRIM([Description]))-LEN(REPLACE(TRIM([Description])," ",""))+1

How does it work?

As we stated above, REPLACE creates a temporary string that has no blanks in it. While on the other hand, TRIM removes all the blanks at the beginning or at the end of the word. So, with the first part of the formula we are getting the exact length of the string without leading or trailing blanks. If we are supposing that all the words are separated by exactly one blank, then the number of words in a string is a deduction between the original length of the string and the length of the temporary string that has no blanks in it, plus 1. Please note, that there is no blank after the last word.

15.   First char is alphabetic

Explanation: If the first character of a string is alphabetic, which mean from “a” to “z” or “A” to “Z”, the result will be TRUE.

Example: Do a check if the first character of the string [Description] is alphabetic, or not.

(LTRIM([Description]) >= "A" AND LTRIM([Description]) <= "Z") OR (LTRIM([Description]) >= "a" AND LTRIM([Description]) <= "z")

How does it work?

LTRIM is an advanced version of the above mentioned, TRIM. While TRIM only removes all leading and trailing blanks, LTRIM removes all the leading blanks as well but also returns the first character in a string. Since we got the first character extracted, we can easily state a condition against which we can compare. The Boolean expressions         (greater than and less than) when combined with AND or OR, help us by checking if the first character is within that character ranges. By using AND, we are creating a range and with the usage of OR, we are giving space for checking within two ranges, upper and lower cases. If the first character is within any of these two ranges, this formula will return TRUE. Otherwise, it will return FALSE.

16.   First char is numeric

Explanation: If the first character of a string is numeric, which means from 0 to 9, the result will be TRUE.

Example: Now the opposite case than the previous one. Do a check if the first character of the string Description, is numeric or not.

ASCII(LTRIM([Description])) >= 48 AND ASCII(LTRIM([Description])) <= 57

How does it work?

As we mentioned above, LTRIM removes all the leading blanks as well but also returns the first character in a string. By using the Boolean expression AND, we are checking if the first character is within the range of 0 and 9. Please note that, 48 is the ASCII code for 0, while 57 is the ASCII code for 9.

17.   Count substrings

Explanation: Count how many times a specified substring occurs in a string.

Example: Check and count how many occurrences does the parameter [Parameter] have in the dimension [Description]

(LEN([Description])-LEN(REPLACE([Description],[Parameter ],""))) / LEN([Parameter])

How does it work?

Till now, we used REPLACE for only removing the blanks. But REPLACE, can be used for removing any type of substring from a certain string. So, in this case, we use the REPLACE function to create a temporary version of the string where we deleted all the occurrences of the [Parameter] in it. When we reduce the length of the temporary version without the occurrence of the [Parameter] from the original length of the string, we get the number of characters of all substrings [Parameter] in the specific string. In order to get the actual number how many times it occurs, we need to divide this number with the length of the substring.

Example: Let us suppose that our search string is “abcd”, that appears three times in the string. The REPLACE statement first of all, deletes the substring “abcd” from the string three times. That being said, twelve characters are deleted in total and the first line of the formula returns 12. Afterwards, if we divide this number with the original length of the searched substring which is four characters, we get the accurate count of occurrence of the substring “abcd” in the string.

18.   Count comma separated items

Explanation:  Count how many substrings are in a certain string that are separated by a comma.

Example: Check how many strings there are in the dimension [Description] which are separated by a comma.

LEN([Description])-LEN(REPLACE([Description],",",""))+1

How does it work?

In this case, we use the same formula as we used for the case ‘Count words’. Just in this case, instead of the blanks, we are replacing it with commas.

Extraction

When you are working with data which is stored as a string on a daily basis, knowing how to extract data from a string field is an essential since in most of the cases, we don’t need the whole length of the field. Extracting data means creating substrings from a total string. The extraction is done by indicating the position from which you want to extract the substring and a specified length of the substring. 

Since Tableau is a tool that offers core base analysis, extraction of data in Tableau can be a very simple process and a very complex one, at the same time. Extraction of data in Tableau presents combining multiple functions that together can help you achieve your goal. There are many scenarios in the Tableau Desktop Certified Associate Certification Exam where such tasks will need to be performed. Please note that string calculations are not the only way how you can create substrings in Tableau. Another way is creating multiple substring from a field is by using the ‘Custom split’ feature. On the Data pane in the worksheet itself, check the menu for Transform, then Split and at the end, Custom Split. Although, this way is very limited of creating substrings since is applicable in only few cases.

As a conclusion, If your aimed substring has a fixed length and a defined starting position, you can easily do this in Tableau. But if your substring has a length that is always varying or its starting point is constantly changing, it is time to upgrade your ‘Tableau game’.

19.   Extract first character

Explanation: Extract the very first character of a given string.

Example: Extract the initials from [FirstName] and [LastName] as being the first character of those string, and concatenate them as one string.

LEFT([FirstName],1) + LEFT([LastName],1)

How does it work?

LEFT function extracts the first character from the left. The addition “1” after the string field, indicates from what position you want your characters extracted, looking from left to right.

20.   Extract first word

Explanation: Instead of extracting only the first character, extract the very first word of a given string.

Example: Extract the first word from the field [Address], which is actually the street number. So let’s suppose that one member of this field is “39 Washington Blvd.” We need to extract all character which are left to the first space in the string.

LEFT(LTRIM([Address]), FIND(LTRIM([Address]+" ")," ") -1)

How does it work?

Let’s start from the second part within the formula. With LTRIM, first we remove all leading blanks. Afterwards, the function FIND returns the position of the first blank inside the string. Since we have found the position of the first blank, we can build the formula outside of it by using LEFT. In addition to the LEFT function, we are using “-1” so we can extract all characters left to the blank, starting one position backwards from it. In order to be sure that this calculation will work even if the string contains only one word, we have added a blank to the first parameter of the FIND statement.

21.   Extract first 2 words

Explanation: From a given string, extract the first two words.

Example: Now, let’s extract the first two words from the field [Address]. That means we should extract all the characters which are left from the second occurrence of a blank, while excluding all leading blanks.

LEFT(TRIM([Address]), FIND(TRIM([Address])+"   "," ", FIND(TRIM([Address])+" ", " ")+1)-1)

How does it work?

First of all, we use TRIM to ensure that all leading and trailing blanks will be removed. As in the previous case, we will start looking from inside the formula in order to get the logic, not from the beginning. The second FIND statement is looking for the position of the first blank. Afterwards, the first FIND statement is looking for a blank starting one character right to the position of the first blank, that we found with the second FIND. As well, in order to ensure that a blank will be found even if the string contains only one or two words, three blanks are added to the trimmed string.

22.   Extract last part

Explanation: Extract all the characters from a string that come after the first word.

Example: Extract the street name from the field [Address]. That being said, we should extract all characters which are right to space after the first word, meaning after the street number.

RIGHT([Address], LEN([Address]) - FIND([Address]," "))

How does it work?

If we deduct the first occurrence of a space from the length of the string, all that remains is the length of the string after the first word. That means, the number of characters that will be returned from the RIGHT function.

23.   Extract mid part fixed

Explanation: Extract a part of a string where the position and the length of the substring are known and fixed.

Example: As substrings, extract the bank identification code and the sort code from an IBAN number. In this case, it is known that the bank identification code are the four characters from position five and the sort code is four characters long starting from position nine. In between insert blanks to make it more readable.

MID([IBAN],5,3) +" " + MID([IBAN],9,3)

How does it work?

As we mentioned above, in this case the bank identification code in an IBAN number exactly starts at position five and is four characters long.In the MID function, first we state the field from which we want to extract, the starting character and the exact length of the substring that we want to extract. Blanks are inserted using string concatenation to make it more readable.

24.    Extract mid part variable

Explanation: Extract a part of a string that has variable position, but still a fixed length.

Example: Create a substring from the middle initials from the full name field. The full name field contains the first name, the middle initials, and the last name afterwards. The middle initials are the two characters after the first blank.

IF CONTAINS ([Name], ".") THEN MID([Name], FIND([Name], " ") + 1, 2) ELSE "" END

 How does it work?

First, by using the IF clause and the CONTAINS function we check if there is a period in the string, which means that the [Name] field contains middle initials. If yes, the formula will return the initials. With the FIND function we search for the first blank and then with the MID function we extract the two characters after the first blank. 

25.   Extract between 2 similar delimiters

Explanation: Extract a part of a string that is between two similar delimiters from a given string.

Example: Create a substring by extracting the short form of a URL from the full URL. For example, extract www.youtube.com from https://www.youtube.com/watch?v=D5Y11hwjMNs

LEFT( MID([Full URL], FIND([Full URL],"://")+3), FIND(MID([Full URL],FIND([Full URL],"://")+3),"/")-1)

How does it work?

The role of the first MID statement is to extract everything that is right to the third character, after the first occurrence of “://. That means everything from “www…”, which is stated in the first FIND statement. Please note that the optional parameter [length] of the MID function is omitted. The next slash in the remaining string is found by the second FIND. And at the end, by using the LEFT function we are able everything from starting from left, which means everything which is result of the first FIND statement till the characters which are returned by the second FIND statement.

26.   Extract 2 different delimiters

Explanation: Extract a part of a string that is between two different delimiters from a given string..

Example: Extract the first name from an email address that comes in a format lastname.firstname@example.com

MID(LEFT([e-mail],FIND([e-mail],"@")-1), FIND([e-mail],".")+1)

How does it work?

The first LEFT statement returns all the characters, starting from left to right up to the delimitator “@”.The additional -1 in this statement means that we should also take the first character left from the sign “@”. From the substring which is returned by the LEFT function, the MID function start extracting at the position of the period. It returns everything that comes after the period, which in our case is the first name.

27.   Extract first word after specified word

Explanation: Create a substring from a given string by extracting the first word after a specified word.

Example: Extract the first word in [Description] after the first occurrence specified in the parameter [String Parameter]. The [String Parameter] in our case is the word that we are using as an identifies after what to extract.

IF FIND(UPPER(" "+[Description])+" ",UPPER(" "+[String Parameter])+" ") > 0 THEN MID(" " +[Description]+" ", FIND(UPPER(" " +[Description]+" "),UPPER(" "+[String Parameter]+" "))+LEN([String Parameter])+2, FIND( MID(" " +[Description]+" ", FIND(UPPER(" " +[Description]+" "),UPPER(" "+[String Parameter]+" "))+LEN([String Parameter])+2)," ")-1) ELSE "" END

How does it work?

First of all, we use the IF clause to check if the certain string contains the word that we have specified, which is the [String Parameter] in our case. We search for the specified word with the first FIND statement. By using the MID function, we are able to extract two positions from the specified word, starting from right to left. This is where we use the second FIND, to find the position of the first character of the specified word plus the whole length of it, and plus 2 which means the blank right to it. The third FIND statement is searching through the remaining string ( the text after the specified word and the blank after it ) for next blank which is actually the end of the word after the specified word. With the addition -1 we are cutting off the blank behind the searched word. So back to the beginning, the IF statement. If the string contains the word that we have specified, the MID function will return the first word after the specified word. By adding blanks at the beginning and at the end of the string and the search string as well, ensure us that the entire words will be found, not only parts of them. While, UPPER functions helps us to make the search case insensitive.

28.   Extract last word

Explanation: From a given string, extract the last word.

Example: From the dimensions [Name] extract the surname, which is actually the second and last word of the string.

RIGHT( RIGHT([Name],LEN([Name])-FIND([Name]," ")), LEN(RIGHT([Name],LEN([Name])-FIND([Name]," ")))- FIND(RIGHT([Name],LEN([Name])-FIND([Name]," "))," "))

How does it work?

Let’s start by the second RIGHT statement. It returns the rest of the string that is after the first blank, which we find by using the FIND function. The substring that we get as a result here, acts as a string in the first RIGHT statement. The number of characters that will be returned is actually the length of this substring deducted by the position of the first blank in this substring. That means the position of the second blank in the original string. Please note that this formula will not be relevant if the string has more than three words.

29.   Extract date from string

Explanation: Extract the date part from a string and afterwards, convert it to a date.

Example: Create a substring from the field [String Date] by extracting the first date found.

DATE(MID([String Date],FIND([String Date],"/") -2,10))

How does it work?

By using the FIND function, we are able to find the first slash in the field (“MM/”). We make sure that the month will be extracted as well, by adding -2 that means that also the two characters before the slash will be extracted. Afterwards, we add 10 to extract all the following 10 characters (“MM/DD/YYYY”). The DATE function convert the overall result to a date from string. Please note, that this formula works only if the date in the string is in the format MM/DD/YYYY. It will also not be relevant if a slash is in the string left to the date. This formula will always extract only the first date in the string.

30.   Remove line feeds

Explanation: From a given string, remove the line feed within it.

Example: From the string [Description], remove the line feeds.

REPLACE([Description]," "," ")

How does it work?

The syntax of the function REPLACE is (string, string, replacement), so using the REPLACE function like this will replace the line feed by a blank. Please note that using CHAR(10) and blank will not work in Tableau. You will have to insert the line feed in the string in the Calculated Field between the quotation marks, which means that the line feed like it is in the formula above is essential.

Conclusion

 String functions enable you to manipulate string data, which means data made of text. In this article, we have went through few practical examples of concatenation, conversion, analysis and extraction of texts that, hopefully, will help you understand string calculations even better and score higher in your Tableau Desktop Certified Associate Certification Exam.

Provide us feedback if some of these calculations helped you out in your work with Tableau. And of course, if you think of any other practical example that may help others, share it!

Overview

Data extraction is one of the best features in Tableau. Extract data is a subset of data that can be used to increase the performance of reports. Extract filters applied to increase performance. 

However, the data extraction is the most useful feature to store the data locally on the drive and can be accessed by Tableau. The Tableau Desktop Certified Associate Certification Exam evaluates on various such concepts related to Data Extraction.

Creating Extract

Extraction of data can be done in the data pane just by Right-click on the data source connection. You will get the window with many options such as edit, refresh, view, etc. Click on the Extract Data option from the list.

If you want to extract all the data instantly then select the Extract option from the list.

Now it will populate with a window as below:

Select All rows to extract all data in a data source and press OK.

After data extraction completes, we can see there are two cylinders with an arrow checked on the data source name. It indicates the data is extracted and we are currently connected to the extracted data set.

Now if you want to save the data source locally, then you can save it just by clicking on the save menu or save as menu in the toolbar.

If anyone wants to replace the published data source, then select Replace Data Source by right-clicking on the data source.

Now before that we need to verify that the published data source will be replaced by the local data source, and then click OK.

Now, Right-click the published data source, and then click Close.

Select File > Save As.

Here, From the Save as a drop-down menu, select Tableau Packaged Workbook (*.twbx).

After the extract of the local copy is created and the packaged workbook saved, you can send your workbook to your colleague.

Apply Extract Filters

To restrict the data while pulling into the Tableau, we need to apply an extract filter on the data source. To apply the Extract filter, follow the below steps:

1. Right-click on the published data source.

2. Click on the Extract Data menu command.

3. On the pop-up window, click on the Add button.

4. Select the field on which you want to apply the filter and then choose the fields from the existing list to filter out.

Now, in data, we can see there is only filtered data is present. Unwanted data is removed.

An extract filter can be applied only if the data is extracted. 

Extract filters can be applied directly from the Data Source page which is at the start of the tableau where there is a button to select extract connection and add the button to apply extract filter there itself.

Remove extract Filter

Removing extract filters is very simple and easy. To remove extract filters, follow the below steps:

1. Go to the data source page where we have two options to select Live and Extract.
2. Click on the Edit button to edit the filter. The window of Extract Data will pop-up. 
3. Select the file which we want to remove from the filter and then click on the remove button from the window.
4. Yeah! We removed the Extract filter applied.

It’s very easy to apply extract filters on measures.

While you are adding a field into the filter, simply select the measures field into the filter.

Just change is that we will get a filter window to select the minimum and maximum range of values of the data.

Adding New Data to Extract

If we want to add more data manually to the extracted data source, then you can select the option Right click on Data -> Extract -> Append Data from File. After this step, just browse the file containing the data and click OK. 

Now, the values and data type of columns in the data source file should be in sync with the existing data.

Extract History

After all the process as we have discussed above, if you want to see the history of the data extraction, then it is easy to check now.

Just click on Data->Extract History.

Here we can check the exact date and time when we had extracted the data and what action we have done on that extract and how many rows are present in the extract data file.

So, Tableau Data Extract is very much important to make dashboards or reports performance faster.

After creating the data extract, we can refresh the existing data with updated data by options Full Refresh and Incremental Refresh. While refreshing your data extract files, we need to aware of the file type which we are updating. 

Suppose if you are updating the .tde file in Tableau version 2020.3, then it will get updated to the .hyper file automatically. The .hyper file is very beneficial and if you want to go to reverse back to your earlier version of the file then it will not possible.

Now, here we will go over some of the ways to refresh the data files and get updated data into reports or dashboards.

Refresh the data extracts in two ways

1. Full Refresh:

When we are refreshing our data, by default it is refreshing fully. That means every row in the data is getting updated as per the recent data present in the database.

The full refresh may take more hours to refresh the data as compared to an incremental refresh. Depending on the size of the extract it defines the period to complete the refresh process. If you are not defining anything on your dataset, what refresh you want to proceed, then usually it will be full refresh.

How we can set up this refresh?

First, we need to publish the data file on Tableau Server just by clicking on Publish data to Tableau Server.

Now, you can specify the Refresh Schedule (Full Extract) to whatever you want. And click on Publish button. After successful completion it will prompts as Publish complete.

2. Incremental Refresh:

As the databases are filling day by day, month by month, most of the time we prefer incremental refresh rather than a full refresh. Incremental refresh will refresh the newly added data. For example, if you have the e-commerce data and the sales data is getting added every day into the database, you want the fully updated data at the weekend, then we will schedule an incremental refresh every weekend. So that we will get all the newly added sales data with existing data on the weekend. Also, if the data structure or anything changes in the existing data, then we should schedule the full refresh before the incremental refresh.

You can follow the below steps to schedule incremental refresh on data:

1. Select Data -> Extract Data.
2. Now in Extract Data, Select All Rows as the number of rows to be selected to refresh. All rows should be refreshed when you are refreshing data incrementally.
3. Select check box of Incremental Refresh and then specify the column in the database by which we can recognize the newly added data in the database.
4. For instance, if you add there Order ID column, then what refresh will do here is that it will refresh the data which is coming after the Order ID from the previous one till which we have refreshed earlier. 

5. After all these steps, click on Extract.
6. In all the above steps we are creating new extracts, defining new extract. If you want to ensure that the data is updating successfully, then we should check the extract history always and ensure that the data is updating with correct data and on time.

The extracted data is stored in TDE file that is Tableau Data Extract.

Advantages of data extract

Performance faster: Data extract helps in building dashboards with less time consuming and performance faster. Also, you do not have to worry about the database location as the data will be present locally on your machine.

Snapshot of Data: You can take snapshot of your data and can connect to live data whenever you want updated data. 

Easy to handle larger data sources: We can create huge data extract file in less time.

Internet connectivity: We can access the data offline and no internet connectivity required to connect to local data. For example, when you are travelling and not able to access any internet connectivity then this extract data file will help you to analyze the existing data.Helps optimizing extracts: When you create data extracts, if we want to create it faster then we need to optimize the extracts. What optimize extract will do is it will check your data and any calculation done within it and do that calculation at the first and avoid the fields to re calculate every time. So, this will be performance optimized.