Documentation Data Pipeline FAQs

Combine columns in Data Explorer

If you joined after January 6, 2020, you might be using our new Visual SQL interface, and this content may not look familiar to you. Check out Combine columns in Visual SQL for more relevant content.

You can combine the data within your columns in a few different ways. There are two ways to combine columns in Chartio using the Data Pipeline:

Combine Columns step

The easiest way is to use a Combine Columns step in the Data Pipeline as shown in the example below. With this method, you specify a new column name and the columns that will be combined.

You can choose to concatenate, add, subtract, or multiply the columns that you are combining in this step. You also have more customization options to define the separator for the combined values. The available separator options are whitespace, comma, colon, dash, and dot.

You also have the option to Hide Combined Columns within the same step. The columns can also be hidden at a later time using a Hide Columns step.

Combine Columns in the Pipeline

Custom formula

If you’d like to customize how the columns are combined, you can do so using a custom formula in the Add Column step. Chartio’s Data Pipeline recognizes standard SQLite expressions. This means you can use the || operator, which allows you to concatenate (combine) values. You can add additional text to the new column by including the text in single quotes. To do this:

Scroll down to the Data Pipeline and click the +Add Transformation button, then choose Add Column.
Name your column, select Custom formula from the Formula Type dropdown, and enter the formula in the following format:
```
"Column Name 1" || "Column Name 2"
```

Note: Be sure to use double quotes when specifying a column name.

Combining columns in chartio

Combining columns with NULL values

When using the Combine Columns Data Pipeline step to combine two columns, if one of the columns is null then the combined column will also be blank. This is because concatenation will ignore a null column and not show it in the result.

Combining columns that have a null value

To address this, you can use a case statement formula in an **Edit Column **step prior to combining the columns.

Write a case statement in an Edit Column Pipeline step

An example case statement formula is as follows:

case when "Column2" is null then 0 else "Column2" end

This will fill in the null values of Column2 with a 0. This will enable us to combine all rows when combining columns.

Combining columns final result