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 (else
value).
Signature
case(bool exp1,result1,...,else)
The following table illustrates the case() function parameters:
Parameter | Description |
---|---|
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 |
Returns
string
, int
, double
, long
, date
, or timestamp
Examples
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,
- Replace
bool exp1
with Sales date from the Data panel, and then add> date("2000-01-17")
. - Replace
result1
with Sales Date. - Add another condition,
SALES.SALES.TIME_ID < date("2000-01-07"),
followed by its subsequent resultdate("2000-01-07"),
- Replace
else
with Sales Date.
- Replace
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.