CASE
CASE is a conditional statement that performs logical tests on a series of conditions and returns values when a condition is met. The CASE function returns a numeric result. You use CASE for complex conditional expressions that contain two or more conditions.
CASE is followed by WHEN/THEN keywords to specify the conditions, then by ELSE to specify the default return.
If no match is found, the default return expression (ELSE) is used. If there is no default return and no values match, then Null is returned. All CASE expressions conclude with the END keyword.
CASE and Multidimensionality
Before proceeding to the CASE syntax samples, as a user you have to understand that usage guidelines for CASE in the MAQL environment (see MAQL - Analytical Query Language) are based on multidimensionality. Understanding multidimensionality is crucial to ensure your CASE metrics return correct results (see MAQL and Multidimensionality).
- CASE does not work with the base data level.
- Instead, the condition is evaluated on the aggregation dimension.
- All WHENs are evaluated independently but all based on a single dimension (the aggregation dimension mentioned in the previous point).
To ensure can identify any errors in your metrics that use the CASE statement early, we recommend the following:
- Upgrade your Extensible Analytical Engine (XAE) to version 3 (for more details, see Upgrading XAE to Version 3).
- Always put the CASE statement inside an aggregation (SUM) container.
Avoid using nested CASE statements in metrics. A CASE statement forces calculations to be done for every single attribute that meets that condition. Anything that does not match the CASE conditions reaches its ELSE clause and has to be processed as well.
CASE Syntax
CASE can be used in the following two syntax forms:
| form | syntax | example |
|---|---|---|
| No aggregation | SELECT CASE WHEN condition1 THEN outcome1, WHEN condition2 THEN outcome 2 ELSE outcome3 END | SELECT CASE WHEN activity_type IN (email, meeting) THEN 1, WHEN activity_type IN (phone_call) THEN 2 ELSE 0 END |
| With aggregation | SELECT SUM (SELECT CASE WHEN condition1 THEN outcome1, WHEN condition2 THEN outcome2 ELSE outcome3 END) | SELECT SUM (SELECT CASE WHEN activity_type IN (email, meeting) THEN Amount ELSE Loss END) |
Using the aggregation container ensure that the CASE conditions are computed on the correct dimension and returns relevant results.
This is why we recommend using SUM-based aggregation in both the XAE1 and the XAE3 environments.
CASE metrics constructed under XAE1 without aggregation might not behave correctly in the XAE3 environment.
How to Use CASE Correctly
As noted above, we advise you to use CASE inside aggregation containers.
Here is an example of incorrect use of CASE in the following logical data model. For the sake brevity, only one CASE condition is used.
If you want to slice the following metric with Campaign Category, the report would not compute in XAE3 because the WHEN conditions are not related to report dimensionality:
SELECT CASE WHEN Customer State IN (AR, NY) THEN SUM (Price) ELSE SUM (Price*Quantity) END
On the other hand, the same metric when sliced by attribute Customer ID (instead of Campaign Category) is valid in all XAE versions. That is because the slicing attribute (Customer ID) is directly related to the CASE condition attribute (Customer State).
If you need to slice by unrelated attribute, put the CASE statement inside the SUM aggregation instead:
SELECT SUM (SELECT CASE WHEN Customer State IN (AR, NY) THEN Price ELSE Price*Quantity END)