CASE statements in categorical dropdowns

This is a really simple example to show you how to use CASE statements in your categorical dropdowns.

Let's say we have a categorical 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 categorical dropdown.  We'll also need to put the CASE statement in our connected chart so they're both using the value "Facebook" when filtering.

 

1. Add CASE statement to categorical dropdown

Here's what our categorical dropdown query looks 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";

 

2. Add CASE statement to chart query

The format for adding a CASE statement to a categorical dropdown is below. Note that any other single quotes inside the CASE statement in parentheses needs to be escaped with a backslash.

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

And here's what our chart query looks like.

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')}
)

As always, please email us at support@chartio.com with any questions.