Concatenation is the process of combining multiple string fields into a single field. The simplest way to do this is to use a formula tool and combine the strings with a + operator. You can also concatenate using aggregation tools, like summarise and cross tab.

Any time you have string information in two or more different fields, you will likely want to concatenate those fields in some way.

How to Concatenate with Alteryx formulas

The simplest way to concatenate 2 fields is with a formula tool. The syntax to combine the strings is to use the + operator. You can use the + operator to concatenate two or more strings, two or more string fields, or a combination of strings and string fields.

Concatenating two string fields.

When combining two string fields, like in Formula 1, the strings are combined exactly as they appear in the individual fields. This means that if there are no spaces in the fields, then no space will separate the resulting field, see the formula preview shows PAMELAWRIGHT.

To force a separator into the resulting field, we can add an additional string like in Formula 2. Adding the space between quotes (it doesn’t matter if the quotes are single or double as long as they are consistent) you can force a space into the final column. In this way, we have a result of PAMELA WRIGHT.

Concatenating a string column with a string

You can use the + operator to add string text to a field like in formula 3. Having the target column [Customer Segment] concatenating with the string ‘ – Transaction’, you can add context to an existing field for future analysis.

This could also be used for creating text descriptions where dynamic content is added for explanation or generating context for subsequent use. In formula 4, the full name (created in formula 1) has the number of visits appended for context use. To use a numeric field (the visits is an integer), it needs to be converted into a string using the ToString function. ToString includes the ability to define the number of decimal places for your purpose.

Concatenate by Aggregation

To allow for more flexibility when concatenating fields, you can use the string aggregation options in Alteryx. The aggregation options allow you to combine different rows into a single row with a defined separator. This is useful if you have multiple records that you want to combine into a single record but is limited by not being able to add any additional context by adding manual strings to the record. If the string you want to combine is not in the target column, it cannot be concatenated using the aggregation method.

Preparing to Concatenate by Aggregation

Because the aggregation method requires the strings you want to concatenate to be in rows, the first step may be to reshape your data set to allow for that to happen.

This reshaping option is most useful when you have many columns that you want to concatenate together. It also works when the number of columns is not consistent.

To reshape the data set, you need to assign the fields you want to concatenate as Data Columns. The fields that show each record would be listed as the Key Column. The result of the reshaping is a table that is long and narrow. The “Name” field that is created won’t be needed later, but it gives us a reference on where the Values came from originally. To learn more about Data Shaping in Alteryx, look at this Data School Blog by Dan Watt.

Concatenate with Summarise

Once we have created the reshaped dataset, we want to concatenate the created “Value” Column with the summarise tool. The configuration process is the same as for any other case where you want to use the summarise tool.

The first step is to choose what fields you want to repeat down the page. This will likely be the same as fields you set as “Key Columns” when reshaping your input data. For the “Value Column”, or the field you want to concatenate, you will choose the string field, and from the Add list, select the String sub-menu and choose concatenate.

Once you add the concatenate field, you get a new configuration option at the very bottom of the summarise configuration when you have the concatenate action highlighted. What this will allow is to define what delimiters that appear in between each record (the default is a comma) and what will appear at the beginning and end of the record (great for creating quoted strings or defining JSON or XML elements)

Concatenate with crosstab

A cross tab has the same data shaping requirement as a summarise tool, but it also allows you to create new columns that will get concatenated. As with the summarise tool, you need to choose the grouping column (the Customer Name for this example) and then the values that would appear in the records (so the Value column). The difference comes in the requirement for a header column. For the example we are using, I added a new column called header with all values equal to 1. This results in a single output column, but if we had more values in that column, you would have more columns with the concatenated values.

Bringing Everything Together.

Concatenating fields together allow you to merge strings, whether those strings are contained in fields, rows or manually added in formulas.

Once you start merging the string fields creating the outputs for flexible searching, creating XML or JSON files, or building compressing information into a single field for loading into data warehouses or storage.

A great use of concatenation is to build them into macros, allowing for some very flexible and capable solutions to deliver anything you might need to create.