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 Apply Formula Action or Formula Column Action with a Custom formula in the Pipeline.
Let’s say we want to alter our query results by transforming “img” to “image”. We’d enter the following in the Formula field:
case when "Type" = 'img' then 'image' else "Type" end
You can also use the CASE statement to alter multiple query results. For example, if we wanted to rename all our numeric campaign IDs to their corresponding names, we would instead use the statement below:
case
when "Campaign Id" = '1' then 'Facebook'
when "Campaign Id" = '2' then 'Adwords'
when "Campaign Id" = '3' then 'Television'
when "Campaign Id" = '4' 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 to perform calculations or set various types of conditions for existing columns or in new columns.
For example, say our first invoice got a 15% refund on their amount—we can use a CASE statement to set a calculation to adjust the amount for just that row. 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:
case
when "Invoice Id" = '1' then ("Amount"-("Amount"*0.15))
else "Amount"
end
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 organizations with Inc or Group at their end of their name who submitted invoices greater than 400, we could use the following CASE statement in a Formula Column:
case
when ("Organization" like '%Inc' or "Organization" like '%Group') and "Amount">400 then 'Flag amount'
else 'OK'
end
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",
case
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.
