Documentation Data Pipeline FAQs

Use CASE statements in queries in Data Explorer

If you joined after January 6, 2020, you might be using our new Visual SQL interface, and this content may not look familiar to you. Check out our Use CASE statements in Visual SQL for more relevant content.

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:

Add 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.