Introduction to Tableau String Functions
Tableau String functions are the functions that are used to manipulate the String, say if you want to extract certain characters, replace, split, trim, reshape, concatenate and so on. Let’s assume a scenario where you have Order and the Sales Data for Customers and you want to analyze the Sales contribution for all the Customers with first name ‘Mark’, but you do not have a separate column called as ‘First Name’ to analyze it separately, so this is where the String functions comes in play, you can use String functions here and you can create a new calculated field called ‘FIRST NAME’ and you can analyze the target customer.
There are various categories of functions that Tableau supports, like Date Functions, Number Functions, Type Conversion functions, Aggregate functions, Logical functions. Each of them has its importance in terms of Data representation and reduces a lot of effort in writing complex calculations and complex expressions. For example, if you want to perform calculations on numbers you would use numeric functions like Power, ceiling, round, if you want to perform date calculations you would use date functions like DATEADD() and DATENAME().
String Functions of Tableau
Now let’s have a look at some of these Functions to get a better understanding of it.
1. LOWER() and UPPER()
These functions will transform the String into lower and upper case respectively.
Syntax:
Lower ( String )
Upper ( String )
First of all, let’s have a look at how to create Calculated Fields.
- Go to Analysis Tab at the Top and Select *Create Calculated Field
- A box appears up asking for writing Calculated Field expression. Give the name for the Calculated Field.
- In the Calculation box enter the expression Lower ( [ Column Name ] ) and click
(Here I am using Product Type Column from my Dataset).
Similarly, Create the Calculated Field for Upper Function.
The Calculated Fields ( Lower() and Upper() ) will appear in the Dimensions on the Left.
Now, first of all, Drag the Column(Product Type in my case) from Dimensions Panel into the rows sections followed by the Lower() and Upper() Calculated Fields. We Notice the Strings being transformed to Upper and Lower Case respectively as shown.
4.5 (3,313 ratings)
View Course
2. REPLACE()
It Searches a string for a substring and replaces it with replacement value. If the substring is not present
In the searched String, The String does not change.
Syntax: REPLACE ( string, substring, replacement )
3. ASCII()
It returns the ASCII code for the first character of the String.
Syntax: ASCII ( string )
4. CONTAINS()
It returns the Boolean value (True or False), TRUE if the substring is contained in the String and FALSE if the substring is not present.
Syntax: CONTAINS ( string, substring )
5. SPLIT()
Split function splits the String separated by delimiters into tokens and assigns a token number to each of the tokens. for eg. A String “RTO-123-MNX-YUH” is divided into tokens RTO, 123, MNX, YUH (tokens) where 1,2,3,4 are token no’s for these respectively. The Part of String you want to retrieve can be retrieved by giving the token no as an argument to split function.
Syntax: SPLIT ( String, Delimiter, Token number )
6. LEN()
It returns the length of a String ( or no. of characters in a given String ).
Syntax: LEN (String)
7. Trim()
It removes the unwanted, extra and trailing spaces from a String.
As shown, all the extra spaces in Product type ‘BINOCULARS’ has been removed.
8. CHAR()
It returns the character associated with the ASCII code given as an argument.
Syntax: CHAR ( Integer )
So, CHAR ( 65 ) returns ‘A’ as an ASCII code for ‘A’ is 65.
9. ENDSWITH()
It returns a Boolean value ( TRUE or FALSE ) if the string ends with the specified substring, FALSE if the string does not end with the specified substring.
Syntax: ENDSWITH (String, Substring)
For eg : ENDSWITH ( ‘FUNCTION’, ’ION’ ) returns TRUE because String ‘FUNCTION’ ends with ‘ION’ .
10. LEFT()
This function returns the characters from the leftmost part of the String.
Syntax: LEFT (String, no. of characters)
For instance let’s have LEFT(‘Tableau’,3)
Output: ‘Tab’ (returns the leftmost 3 characters)
11. RIGHT()
This function returns the characters from the rightmost part of the String.
Syntax: LEFT (String, no. of characters)
For instance let’s have RIGHT (‘Tableau’,3)
Output: ‘eau’ (returns the rightmost 3 characters)
Conclusion
Tableau is a self-service Business Intelligence tool where you can do complex analysis just with a drag and drop functionality of it. Preparing the data for analysis, cleansing of data, pivoting, data blending from multiple sources, create sets and bins, dynamic dimensions and measures, beautiful visualizations and moreover, its user-friendliness makes it the perfect enterprise solution. Every business domain is making extensive use of it to deliver insightful visuals to its clients in achieving their business goals.
We can build beautiful and interactive visuals and Dashboards by connecting them with various R and Python libraries. We can view it on any device, Tableau is smart enough to understand the device and adjust the size of the report according to device size without any extra coding efforts. Like any other Programming/scripting Languages, Databases and BI Tools. Tableau also provides some inbuilt functions to modify and format String data. Awareness for String functions is always required if you want to be an excellent developer. With these functions, you can manipulate your string data in whatever way you want to represent it.
This function saves a lot of time and effort the data analyst has to make in making a specific analysis of some business areas. I hope you all got a Fair idea on how to implement String Functions in Tableau.
Recommended Articles
This is a guide to Tableau String Functions. Here we discuss the various categories of String Functions of Tableau with syntax. You may also have a look at the following articles to learn more –
