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:

ParameterDescription
bool expThe condition to be evaluated
exp1The value returned if the condition evaluates to true
exp2The 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".
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.