Built-in Functions → if
if() is a conditional statement that evaluates a condition and returns the first expression (
exp1) if the condition is true, otherwise, it returns the final expression (
if(bool exp, exp1,exp2)
The following table illustrates the if() function parameters:
|bool exp||The condition to be evaluated|
|exp1||The value returned if the condition evaluates to true|
|exp2||The value returned if the condition evaluates to false|
Evaluate the COUNTRY_NAME column for instances of “Spain”. For Spain, return “Great”, otherwise, return “Other_Country”.
if(SALES.COUNTRIES.COUNTRY_NAME = "Spain","Great","Other_Country")
Use the following steps for detailed instructions on how to use the if() conditional statement to tackle the above example 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 to the respective trays:
- Country from the Countries table to the Grouping Dimension tray
- Revenue from the Sales table 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 if function,
if(bool exp, exp1,exp2), to add the formula to the editor.
- In the Formula Editor,
bool expwith Country from the Data panel, followed by = “Spain”.
if(SALES.COUNTRIES.COUNTRY_NAME = "Spain", "Great", "Other_Country")
- Select Validate & Save.
- In the Grouping Dimension tray, double-click the New Formula pill and rename it to if().
- Name the insight Revenue Per Country.
- In the Action bar, select Save.