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.
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 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.
To address this, you can use a case statement formula in an **Edit Column **step prior to combining the columns.
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.