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).

Note

Starting with the Incorta Cloud 2022.1.0 Release, the case() conditional statement supports date and timestamp as parameters and return data types.

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:

Note

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 result date("2000-01-07"),
  - Replace else with 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.

Content

Signature

Returns

Examples