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 Action
You can use a Calculated Column > Custom formula to customize how your columns are combined. Chartio’s 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.
Here are the basic steps to accomplish this:
- Click the Calculated Column above the Result Table and select Custom as the formula type.
Enter the formula in the following format:
"Column Name 1" || "Column Name 2"
Note: Be sure to wrap column names in double quotes and static string in single quotes.
Combining Columns with NULL Values
When using a Custom formula to combine two columns, the combined column will be blank if one of the columns is null. This is because concatenation ignores a null column and doesn’t show it in the result.
To address this, you can use a CASE statement formula in an Edit Formula Action prior to combining the columns.
For this example, we want to see when a user signed a document. If there are any null values for Signature Date, we want to fill it with the string ‘did not sign’. Here’s how that CASE statement would look like:
case when "Signature Date" is null then 'did not sign' else "Signature Date" end
Now any null values in the Signature Date column are filled with ‘did not sign’. This enables us to combine all rows when combining columns.
Here’s another CASE statement we could use to customize the message when the user hasn’t signed yet:
case when "Signature Date" != 'did not sign' then "First Name" ||' signed at ' || "Signature Date" else "First Name" || ' ' || "Signature Date" end