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 (exp2
).
Signature
if(bool exp, exp1,exp2)
The following table illustrates the if() function parameters:
Parameter | Description |
---|---|
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 |
Returns
string
, int
, double
, or long
Example
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:
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 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,
- Replace
bool exp
with Country from the Data panel, followed by = “Spain”. - Replace
exp1
with “Great”. - Replace
exp2
with "Other_Country".
- Replace
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.