10 Dec Tableau – String Calculations

String calculations in Tableau are used to manipulate and analyze text data. Let us see some examples to understand the concept.

Types of String Calculations

The following are some common types of string calculations. Consider fields as Field A, Field B, etc.

Basic String Calculations

1. Concatenation: Field A + ‘ ‘ + Field B (combine strings)
2. Substring: SUBSTRING(Field A, 1, 5) (extract part of string)
3. Length: LEN(Field A) (string length)
4. Upper/Lower Case: UPPER(Field A) or LOWER(Field A) (change case)
5. Trim: TRIM(Field A) (remove leading/trailing spaces)

Text Manipulation Calculations

1. Replace: REPLACE(Field A, ‘old’, ‘new’) (replace substring)
2. Split: SPLIT(Field A, ‘,’) (split string into array)
3. Join: JOIN(Field A, ‘,’) (combine array into string)
4. Parse: PARSE(Field A, ‘format’) (extract data from string)

Regular Expression (RegEx) Calculations

1. REGEXP_MATCH: REGEXP_MATCH(Field A, ‘pattern’) (match pattern)
2. REGEXP_REPLACE: REGEXP_REPLACE(Field A, ‘pattern’, ‘replacement’) (replace matches)
3. REGEXP_EXTRACT: REGEXP_EXTRACT(Field A, ‘pattern’) (extract matched text)

Date and Time String Calculations

1. DATEPARSE: DATEPARSE(‘format’, Field A) (convert string to date)
2. DATETOSTRING: DATETOSTRING(Field A, ‘format’) (convert date to string)
3. TIMESTAMP: TIMESTAMP(Field A) (convert string to timestamp)

Advanced String Calculations

1. Find: FIND(Field A, ‘substring’) (find substring index)
2. Contains: CONTAINS(Field A, ‘substring’) (check if substring exists)
3. StartsWith/EndsWith: STARTSWITH(Field A, ‘substring’) or ENDSWITH(Field A, ‘substring’)

Example

Let us create a new sheet and name it String Calculations in the same Amit_Tableau_Calculations.twb Tableau Workbook. We created the Amit_Tableau_Calculations.twb Tableau workbook in the Numeric Calculations lesson.

Go to the Analysis menu and click on it. After that, click Create Calculated Field:

Click on the arrow to display all the built-in functions. From the dropdown, click String to display only the string functions:

Set the name of the calculation. We will set it to City_C to find the sales of the city that begin with the letter C.

From the right, select the STARTSWITH() function. After selecting, on the left, set the formulae for the cities starting with the letter C i.e.,  STARTSWITH([City],’C’).

The formulae can be seen and there are no error messages. Click OK:

On the left, the new calculation field is visible i.e.,  City_C. The STARTSWITH() returns True if the string starts with the mentioned substring i.e., in this case, it was the letter C:

Drag the Sales to the Columns shelf, and the new City to the Rows shelf.

After dragging, the bar chart will be visible as shown below.

Drag the new field City_C to the Filters section. After dragging, only the True field is checked and that is fine for us. Click Ok:

Now, only the True values are visible i.e. the sum of sales for the cities starting with the letter C. Keep the mouse cursor on any of the bars to get the details:

If you liked the tutorial, spread the word and share the link and our website Studyopedia with others.

For Videos, Join Our YouTube Channel: Join Now

Read More:

• Get Data from Excel in Power BI
• Create a Line Chart in Tableau
• Create a Bar/ Column Chart in Tableau
• Create a Pie Chart in Tableau
• Create a Map in Tableau
• Create a Tree Map in Tableau
• Create a Scatter Plot in Tableau
• Create a Bubble Chart in Tableau
• Tableau – Filters and its types
• Tableau – Filter Dimensions
• Tableau – Filter Measures
• Tableau – Filter Dates
• Create an Area Chart in Tableau
• Create a Heat Map in Tableau
• Create a Histogram in Tableau