How to concatenate strings and numbers in Tableau

Portland, OR, USA - Jan 12, 2022: Closeup of Tableau logo seen on its homepage on a laptop computer. Tableau is an interactive data visualization software company focused on business intelligence.
Image: Tada Images/Adobe Stock

Tableau is a powerful data visualization tool that is growing in popularity and enjoying a healthy market share. One of the reasons for its popularity is its ease of use. Even users with no coding experience can create beautiful and interactive complex visualizations using Tableau. The fact that it integrates well with many different data sources is another reason for its rising popularity.

One of the most commonly asked questions by users is how to concatenate in Tableau. If you have ever wanted to concatenate two fields together in Tableau, it’s actually a lot easier than you might think.

Understanding concatenation

What is concatenation? In everyday English, to concatenate means to join together. In Tableau, concatenation is the process of combining two or more fields into a single field. For example, you could concatenate a customer’s first and last name into a single field. The result would be a single field containing the customer’s full name.

There are several reasons why you might want to concatenate fields in Tableau. For one, it can make your data easier to work with. For example, if you’re trying to create a list of all your customers’ full names, it would be much easier to do if those names were stored in a single field. Additionally, concatenation can help you save space in your database. If you store customer names as two separate fields (first and last name), that takes up twice as much space as storing them in a single field.

SEE: How to use Tableau: A guide for beginners (TechRepublic)

Finally, concatenation can be used to create “calculated fields,” which is a field that is generated by Tableau based on the values in other fields. For example, you could use concatenation to create a field that contains the full address of each customer (i.e., street address, city, state, ZIP code). By concatenating multiple fields into a single field, you can make it much easier to work with your data in Tableau.

Steps to concatenate in Tableau

To demonstrate, we are using a simple data table that has six columns (Figure A): first name, last name, street address, city, state and ZIP code.

Figure A

dataset including first name, last name, street, city, and ZIP code fields
Figure A: The dataset we plan to concatenate in Tableau.

Prepare your dataset

The first step is to prepare your dataset. The data needs to be in a compatible format such as  .xls or .csv. You may also need to check and edit issues such as date formats and column headers to make sure that the data is ready to be used in Tableau.

Load the dataset

The next step is to connect the dataset to Tableau. There are three methods that you can use to load a dataset into Tableau: uploading a file, using a connector or using an accelerator. The first step is to click New Data Source on the Data Menu (Figure B).

Figure B

Data menu option for New Data Source in Tableau
Figure B: Select New Data Source under the Data Menu tab.

This will open the Connect to Data window, where you can select the file you want to use.

Uploading a file is the simplest method—you can just drag and drop the file into Tableau. Using a connector is slightly more complicated, but it allows you to connect Tableau to a range of different data sources (including Salesforce, Google Analytics and Amazon Redshift).

Accelerators are prebuilt dashboards you can use to quickly visualize your data. They are best for inspiration and ideas and you can also swap the existing data with your own. To use an accelerator, simply select the Accelerators option from the New Data Source menu.

For this demonstration, we are going to use the Upload a File option (Figure C). Simply drag and drop the dataset into Tableau.

Figure C

uploading a file into a Tableau dashboard
Figure C: Upload a file to Tableau by dragging and dropping the dataset into an empty sheet.

Understand the data

Now that you have loaded the dataset into Tableau, it’s time to take a look at the data and understand what it contains. The first thing you should do is open the Data Source page (you can find this under Data in the top menu bar).

The Data Source page will show you all of the fields in your dataset as well as their data types. It’s important to understand the data types of your fields because it will determine how Tableau treats them when you start building visualizations. For example, drag the first name and last name fields into the rows shelf to view the data (Figure D).

Figure D

viewing first and last name fields in the Tableau Rows preview
Figure D: Preview your data fields to ensure your data and its types are set appropriately.

Concatenate multiple fields

Now that you understand the data, let’s concatenate the first name and last name fields to create a single field for the people’s names. To do this, click the arrow next to the search button and click Create Calculated Field from the menu dropdown (Figure E).

Figure E

Create Calculated Field menu option in Tableau
Figure E: Select Create Calculated Field to begin concatenating data in Tableau.

In the new window,  give the field a name (we’re calling it Full Name). Drag the First name field into the box and then the Last name field. Ensure you have a separator (+ ”  ” +) as shown in Figure F.

Figure F

putting a separator between fields in the concatenate pop-up field
Figure F: Drag the fields you want to concatenate into the pop-up and put a separator between each one.

You need to put a separator between the first name and last name fields when you concatenate them, so Tableau knows they are two different pieces of information. This will concatenate the first name and last name fields into a single field called Full Name. Now, if you drag this new field into the rows shelf, you will see that the data is displayed differently (Figure G).

Figure G

previewing the concatenated data in Tableau
Figure G: Test your newly concatenated data.

Now, let’s repeat the same process for the street address, city, state and ZIP code. Once more, click Create Calculated Field. Rename the field (e.g. Address), drag the four fields into the editor window and add a separator between each field (Figure H).

Figure H

adding separators between the address fields in the concatenate pop-up field
Figure H: Repeat the process to concatenate the address fields.

This will concatenate the various address elements into a single field. Once the formula is entered, click OK to apply it. The new field will now appear in the data pane and can be used like any other field in Tableau. Drag it into the row shelf to see the new data (Figure I).

Figure I

Previewing the concatenated data for Full Name and Address in Tableau
Figure I: View the concatenated data for Full Name and Address.

Concatenating non-string fields

One potential issue you may encounter when working with data in Tableau is concatenating non-string fields. For example, let’s say you have a field for ZIP code that contains only integers. If you try to concatenate this field with another string field, you’ll get an error message.

To avoid this problem, you must convert the integer field into a string using the STR function. For example, in our data above, we converted the ZIP code into a string. Once you’ve done this, you’ll be able to concatenate the fields without any issues. Keep this in mind next time you’re working with data in Tableau, and you should be able to avoid any potential problems.

Source link