Combine columns in Visual SQL

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

Combine Columns Action

Combine columns using a Custom formula

You can use a Formula 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 Formula 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 Apply 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