Built-in Functions → case
case() is a conditional statement that returns a result based on multiple
if conditional statements. For a pair of conditions, case() evaluates the first condition and returns its subsequent value if the evaluation is true. Otherwise, it evaluates the second condition and its subsequent value, and so on. If none of the cases are true, the function returns the final expression (
The following table illustrates the case() function parameters:
|bool exp1||The condition to be evaluated|
|result1||The value returned if the first condition is met|
|else||The final expression returned if none of the conditions are true|
- Evaluate the COUNTRY_NAME column for instances of “Spain” and “Argentina”. For Spain, return “Great”, and for “Argentina”, return “Cool”. Otherwise, return “Other_Country”.
case(SALES.COUNTRIES.COUNTRY_NAME = "Spain","Great",SALES.COUNTRIES.COUNTRY_NAME = "Argentina","Cool","Other_Country")
- Evaluate your sales date to determine whether it occurred before or after the given date
"2000-01-07". If the sales occurred after the given date, return sales date, and if before, return the given date. Otherwise, return the sales date.
case(SALES.SALES.TIME_ID > date("2000-01-07"),SALES.SALES.TIME_ID,SALES.SALES.TIME_ID < date("2000-01-07"),date("2000-01-07"),SALES.SALES.TIME_ID)
Use the following steps for detailed instructions on how to use the case() conditional statement to tackle the second example above in an insight:
In the Cluster Management Console (CMC), you can create a tenant that includes Sample Data. The Sample Data includes the SALES schema.
- In the Navigation bar, select the Content tab, and then select + New → Add Dashboard.
- In the Add Dashboard dialog, for Name, enter Product Dashboard, and then select Add.
- In the Action bar, select + (add icon), or select + Add Insight.
- In the Insight panel, select Listing Table or V.
- In Tables, select Aggregated Table.
- In the Data panel, select Manage Dataset.
- In the Manage Data Sets panel, in Tables, select SALES. Close the panel.
- From the Data panel, drag and drop the following columns from the SALES table to the respective tray:
- Sales Date to the Grouping Dimension tray
- Revenue to the Measure tray
- From the Data panel, drag and drop Add Formula to the Grouping Dimension tray.
- The Formula Builder automatically opens:
- In Search Functions, select the down arrow, and then select Conditional Statements.
- Double-click the second case function,
case(bool exp1,result1,...,else), to add the formula to the editor.
- In the Formula Editor,
bool exp1with Sales date from the Data panel, and then add
result1with Sales Date.
- Add another condition,
SALES.SALES.TIME_ID < date("2000-01-07"),followed by its subsequent result
elsewith Sales Date.
case(SALES.SALES.TIME_ID > date("2000-01-07"), SALES.SALES.TIME_ID, SALES.SALES.TIME_ID < date("2000-01-07"), date("2000-01-07"), SALES.SALES.TIME_ID)
- Select Validate & Save.
- Name the insight Sales Date Per Category.
- In the Action bar, select Save.