What is Concatenate in Tableau?
Concatenate in Tableau refers to combining two or more data fields of the same or different data types into a new single field. Tableau supports the concatenation of different data types, such as string with non-string values or string with numeric, etc. The concatenated field created in Tableau can be further used in calculations or reporting. For example, suppose you are sourcing data from multiple files and want to create an identifier for traceability. In that case, you can concatenate the file name and data source to create a unique identifier. Similarly, you can concatenate the first name and last name of a customer to create a Customer Name field for your reporting.
A sample concatenate in Tableau names is provided below. It will create a new calculated field, i.e., Customer Name, that combines both FirstName and LastName.
[FirstName] + ” ” + [LastName]
Table of contents
Key Takeaways
- Concatenate in Tableau allows you to combine two or more data fields of the same or different data types into a new single field
- Tableau supports the concatenation of different data types such as string with non-string values or string with numeric etc.
- To create a concatenate in Tableau, follow the syntax applicable to the concatenate function or operator you want to use.
- You can further apply separators or conditional statements when concatenating fields in Tableau.
Syntax
Tableau doesn’t have a single concatenation function. So, the syntax varies depending on what operator or function you are using for concatenation.
For example, if you want to concatenate a Tableau string and number, then you can use the syntax:
STR([Transaction ID])+ ” : “+ [Product Name]
STR() function will convert a numeric field into a string value.
Similarly, you can concatenate in Tableau with a string and date by converting the date into a string using the STR() function.
How to concatenate in Tableau?
To concatenate in Tableau, you may follow the instructions provided below:
Step 1: Open Tableau public desktop on your machine. Click on File – Open to import the dataset into the Tableau interface.
Step 2: Navigate to a new worksheet in Tableau. Create a calculated field by choosing one of the options below.
- Right-click on the Data pane and select Create Calculated Field…
- Navigate to Analysis – Create Calculated Field.
Step 3: In the Calculation Editor, specify the name of the calculated field and enter the + operator to concatenate two strings.
Click on OK to save the changes. Tableau will create the calculated field, as shown below.
Step 4: Drag and drop the newly created concatenated field to the Tableau view to create a visualization.
In the next section, we will look at a few examples to understand concatenation in Tableau.
Examples
In this section, we will demonstrate different scenarios where you can concatenation fields in Tableau.
Example #1 – Concatenate String with Non-string Values
In this example, we will demonstrate concatenating strings with non-string values in Tableau public using the Titanic dataset, which contains information about the passengers on the Titanic, such as their demographic information, class, fare, and whether they survived the disaster.
Follow the step-by-step guide outlined below:
Step 1: Connect the Titanic dataset to the Tableau interface using the File Navigator.
Step 2: In a new worksheet tab, navigate to Analysis – Create Calculated Field.
Step 3: In the calculation editor dialog box, create a calculated field by specifying a name. Then, enter the concatenation syntax.
Here, we have created a field called Passenger Details by concatenating Passenger ID and Name. We have also added a separator ‘:’ for this field. Once complete, click on OK.
Step 4: Create a visualization by dragging and dropping the concatenated field, i.e., Passenger Details to the Tableau view. As you can see, Tableau has created the calculated field, as shown below.
Step 5: Sort the data based on the Total Fare.
Example #2 – Concatenate a date and DateTime Fields
In this example, we create a calculated field by concatenating a date and DateTime fields in Tableau public using the Flight Delays dataset.
To create a calculated field by concatenating a date and DateTime fields in Tableau public, follow the instructions below:
Step 1: Import the Flight Delays dataset in Tableau public desktop using File – Open.
Step 2: In a new worksheet, right-click on the Data pane. Choose Create Calculated Field…
Step 3: In the calculation editor, specify the name and logic for the concatenation.
Here, we have created a calculated field Flight Deparature Datetime based on Flight Date and Actual Departure Time.
We have used the STR() function to convert the Flight Date into a string datatype. Additionally, we have used DATEPART to extract hour, minute, and second values from the Actual Departure Time field. Finally, we are converting the concatenated values into a datetime format using DATETIME.
Click on OK to save the changes.
Step 4: Similarly, create another calculated field by concatenating the Flight Date and Actual Departure Time as shown in Step 3.
Step 5: Drag and drop the newly created calculated fields to the Rows shelf. You will see the concatenated fields in the view.
Example #3 – Using the STR() function to concatenate non-string fields
In this example, we have created a calculated field using the STR() function to concatenate in Tableau non-string fields using the Monthly Sales for a Souvenir Shop dataset, which contains monthly sales for a souvenir shop at a beach resort town in Queensland, Australia, for January 1987-December 1993.
To create a calculated field using the STR() function to concatenate non-string fields, follow the steps outlined below:
Step 1: From the Tableau public interface, connect the Monthly Sales for a Souvenir Shop dataset.
Step 2: Right-click on the Data pane and choose Create Calculated Field…
Step 3: In the calculation editor window that opens, name the calculated field. Define the logic for this field.
We have created a calculated field Timeline by concatenating Month and Year.
The Year field is numeric, whereas the Month field is string. Hence, we have used the STR() function to convert the numeric field to a string. Click on OK to save the changes.
Tableau will create the calculated field saved to the dataset, as shown below.
Step 5: Drag the Timeline field to the Rows shelf.
Step 6: Sort the view as per the Sales values in descending order by clicking on the Sort icon.
Example #4 – Concatenate Using the ‘+’ Operator
In this example, we will demonstrate creating a calculated field by concatenating two fields using the + operator in Tableau public. For this demonstration, we will use the Waterloo Enrolment dataset containing the headcount of students per academic term across various programs in a fiscal year.
Follow the step-by-step guide below to understand the concatenate operator +.:
Step 1: Import your dataset into the Tableau interface by navigating File – Open. You can view the imported dataset in the Data Source tab.
Step 2: Navigate to Analysis – Create Calculated Field…
Step 3: Specify the name of the field and calculation logic in the calculation editor. Click on OK.
Tableau will automatically create the field seen in the Data pane.
Step 4: Drag and drop the newly created calculated field to the view to create your visualization.
Step 5: Sort the visual based on the Student Headcounts, as shown below. Now, your visualization is ready to be distributed.
Example #5 – Create Dynamic Titles or Headers
In this example, we will create dynamic Titles or Headers in Tableau public using the Europe Sales Records dataset. The Europe Sales Records dataset shows the sales details across various item types across different countries in Europe, such as Order Date, Order ID, Order Priority, Sales Channel, Revenue, Cost, etc.
Step 1: Connect to the Europe Sales Records dataset from your Tableau interface. The imported data can be viewed in the Data Source tab.
Step 2: In a new worksheet, create a visualization by dragging the fields to the Tableau view. Here, we have included Item Type and Total Revenue in the view.
Step 3: Drag the Total Revenue to the Label. Sort the visual based on the Total Revenue.
Step 4: Apply filter to the visual by dragging the Item Type to the Filters pane. Click on OK to save the changes.
Step 5: Right-click on the item, Type field, in the Filters pane, and choose Show Filter. Tableau will display the Item Type values in the right corner of the Tableau view.
Step 6: Right-click on the Item Type field in the Filters pane. Choose Apply to Worksheets – All Using This Data Source.
Step 7: Navigate to a new worksheet. Drag the Item Type to ‘Text’ on the Marks card.
Step 8: Drag the Item Type field to the Filters pane. Click on OK.
You can see the Item Type field in the Filters pane.
Step 9: Right-click on the Item Type in the Filters pane and choose Show Filter.
Step 10: Right-click on the Title. In the Edit Title screen, provide a text.
The edited text can be visible in the header.
Step 11: Navigate to a new dashboard.
Step 12: Drag previously created visual worksheets to the view. Here, we have dragged Example 5 and Example 5 contd to the view, as shown below.
Step 13: Adjust the view to display the chart, as shown below.
Step 14: Apply filter to the Item Type by choosing the relevant values. You will notice the changes in the Header along with the chart, as shown below.
Importance Things to Note
- Always ensure that you follow the right syntax when creating a calculated field for accurate calculations and processing.
- It is recommended you apply concatenate in Tableau prep before you use them on the Tableau desktop.
Frequently Asked Questions (FAQs)
Yes, there are a few limitations to concatenation in Tableau. They include:
• If there are any null values in the dataset, they need to be properly handled to ensure accurate results.
• Concatenation may lead to unexpected results or errors if there is datatype incompatibility.
Yes, you can customize the separator when concatenating fields in Tableau. For example, you can use the + operator for concatenation and specify additional separators such as –,:, _, etc.
[Order Id] + “ : ”+ [Description] or [Product Id] + “_”+ [Product Description]
Yes, you can use conditional statements with concatenation in Tableau. There are multiple conditional statements such as IF-THEN, CASE, IIF, or other functions.
For example, the below conditional statement allows you to concatenate in Tableau based on null values
IF ISNULL([First Name]) THEN [Surname]
ELSE [First Name] + ” “+ [Surname]
END
The key differences between concatenation and blending data in Tableau include:
Concatenation involves combining two more data fields into a single field in the same dataset. You can use concatenation as part of calculated fields or dynamic titles/headers.
Blending involves joining data from different data sources based on a relationship between the datasets. Tableau automatically defines a blended relationship for the data sources; however, you can manually define the relationship as well.
Download Template
This article must help understand the Concatenate in Tableau formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Concatenate in Tableau. Here we learn how to concatenate in Tableau with syntax, examples, points to remember & template. You can learn more from the following articles –
Leave a Reply