Documentation Visual SQL (beta) FAQs

Use CASE statements in Visual SQL

CASE statements use conditional logic to alter query results or to perform calculations on your query results. It’s a flexible SQL and SQLite expression that can be used anywhere a query is set in Chartio. It can also be combined with other functions or formulas.

There’s a lot you can do with CASE statements, and you can incorporate various columns into these or use them as part of other formulas.

Take a look at SQL and SQLite documentation online for additional examples using CASE statements or sign up for one of our webinars on using SQLite in the Data Pipeline.

Below are some examples where you could use CASE statements in Chartio:

In the Pipeline

To alter values or strings

You can add a similar CASE statements as above in your Visual Mode charts by using the Edit Column Action or Calculated Column Action with a Custom formula in the Pipeline.

Let’s say we want to alter our query results by transforming “FB” to “Facebook”. We’d enter the following in the Formula field:

case when "Campaign Id" = 'FB' then 'Facebook' else "Campaign Id" end

Edit your column's results using a CASE statement

You can also use the CASE statement to alter multiple query results. For example, if we wanted to rename all the results here, we would instead use the statement below:

	when "Campaign Id" = 'FB' then 'Facebook'
	when "Campaign Id" = 'AW' then 'Adwords'
	when "Campaign Id" = 'TV' then 'Television'
	when "Campaign Id" = 'WM' then 'Web'
	else "Campaign Id"

Tip! If you’re looking to do this for multiple charts and are not able to correct your results in your database directly, consider creating a Custom Column in your Data Source Schema instead of creating a CASE statement in every chart.

Edit multiple results using a CASE statement

To perform conditional calculations

You can also use a CASE statement to perform calculations or set various types of conditions for existing columns or in new columns.

For example, say we got a 15% refund on our costs for Facebook campaigns, we can use a CASE statement to set a calculation to adjust the cost for just those rows. Doing this enables you to transform your data using values that aren’t stored elsewhere or that you may want to present differently than what’s in your database.

In this case, we’d use the following statement:

	when "Campaign Id" = 'FB' then ("Cost"-("Cost"*0.15))
	else "Cost"

Add a column to calculate adjusted cost using a CASE statement

Multiple conditions per case

You can use ANDs and ORs in your CASE statements to check if a result matches multiple conditions before performing an operation on it.

For example, if we wanted to flag any Facebook (FB) or Adwords (AW) campaigns that exceeded our budget, we could use the following CASE statement in a Calculated Column:

	when ("Campaign Id"='FB' or "Campaign Id"='AW') and "Cost">4000 then 'Exceeded budget'
	else 'OK'

In queries

Conditional SELECT statements using Controls

The following example pulls the “Campaign Id” and “Cost” columns from the Marketing table in our SaaS Company Demo Data data source. Let’s say we also have a Text Input Control on our dashboard, and we want to allow the user to choose the type of aggregation performed on the costs of each campaign ID by typing either Average, Minimum, or Maximum into the Text Input. If none of those options is used, the default is to get the total sum of costs for each campaign ID. To do this, we can modify the SELECT clause in SQL Mode with the following:

SELECT "Marketing"."campaign_id" AS "Campaign Id",
	when {TEXT_INPUT} = 'Average' then AVG("Marketing"."cost")
	when {TEXT_INPUT} = 'Minimum' then MIN("Marketing"."cost")
	when {TEXT_INPUT} = 'Maximum' then MAX("Marketing"."cost")
	else SUM("Marketing"."cost")
end as "Cost"

where TEXT_INPUT is the name of our Text Input Control.

Note: Our Text Input in this example does not have Multi-value selected in its settings.