CASE statements in queries

Variables

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.

Below are a few use cases where you could use this expression in Chartio:

Adding a CASE statement to a Dropdown

Dropdowns on dashboards are set up using Data Source queries. If results within your database aren’t formatted how you’d like them to be displayed within your Dropdown, you can edit your values using a CASE statement.

Let’s say we have a Dropdown with various marketing campaign IDs. One of them is “FB”, and we want to create a CASE statement to have this visible as “Facebook” in our Dropdown.  We’ll also need to put the CASE statement in our connected chart so they’re both using the value “Facebook” when filtering.

Here’s what our categorical dropdown query would look like:

select case m.campaign_id
           when 'FB' then 'Facebook'
           else m.campaign_id
       end as "Campaign Id"
from "public"."marketing" as m
group by "Campaign Id";

Case statments in categorical dropdowns

Adding a CASE statement within a SQL Mode chart query

You can also add a CASE statement directly in your SQL Mode queries within your chart to edit how your results will be displayed or within your filters.

In this example, we’re adding a CASE statement within the filter for our Dropdown to make sure the FB campaigns are included in our results using the format below:

Note: Any other single quotes inside the CASE statement in parentheses need to be escaped with a backslash.

where {CATEGORICAL_DROPDOWN_NAME.IN('CASE statement here')}

Here’s what our chart query looks like using the same Campaign Id column as in the previous example:

select count(m.campaign_id) as "Count of Campaigns"
from "public"."marketing" as m
where (
  {CASE_TEST.IN('case m.campaign_id when 'FB' then 'Facebook' else m.campaign_id end')}
)

Adding a case statement to your SQL Mode query

Adding a CASE statement in the Pipeline

To alter values or strings

You can add a similar CASE statements as above in your Interactive Mode charts by utilizing the Edit Column step with a custom formula in the Data Pipeline.

If we would like to alter our query results, here transforming “FB” to “Facebook” again, 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 of the results here, we would instead use the statement below:

case 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" end

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.

To perform conditional calculations

You can also use a CASE statement in order 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 would use the following statement:

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

Add a column to calculate adjusted cost using a CASE statement

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.


Related Variables Help Articles

See more