Document toolboxDocument toolbox

Conditional (ifthenelse)

Description

Conditional operation that returns a specified value if the column given as first argument (must be a Boolean field) shows true, and another value if it shows false.

How does it work in the search window?

Select Create column in the search window toolbar, then select the Conditional operation. You need to specify three arguments:

Argument

Description

Data type

Argument

Description

Data type

if mandatory

Boolean field that will be used as the base of your condition.

boolean

then mandatory

The value you specify here will be returned if the value of the Boolean column is true.

Any

else mandatory

The value you specify here will be returned if the value of the Boolean column is false.

Same than or compatible with the data type selected in then (for example, integer and float are compatible types)

The data type of the output values depends on the types selected in the then and else arguments.

Example

In the demo.ecommerce.data table, we want to add a column that shows the string Client error responses when the values in our statusCode column are 4xx or 5xx codes, and Succesful response when they are 2xx. To do it, we will create a new column using the Conditional operation but first, we need to create a Boolean column that detects client error responses.

Step 1: Detect client error responses

The first step is creating a Boolean column that shows true when the codes in the statusCode column are 4xx or 5xx, and false when they are 2xx. To do it, we will add a new column using the Greater or equal (ge, >=) operation. Fill the arguments as follows, and enter a name for the column (statusCode_type):

  • Value - statusCode column

  • is greater than or equal to - Click the pencil icon and enter 400

Step 2: Create a new column using the Conditional operation

Select Create column on the query toolbar, then select Conditional as the operation. Fill the arguments as follows, and enter a name for the column (responseType):

  • if - statusCode_type column

  • then - Click the pencil icon and enter Client error response

  • else - Click the pencil icon and enter Successful response

Click Create column and you will get the following result:

How does it work in LINQ?

Use the operator select... as...  and add the operation syntax to create the new column. See below the syntax for the Conditional operation. Keep in mind that value1 is the value that will be returned when the Boolean column shows true, and value2 when it shows false.

  • ifthenelse(boolean, value1_true, value2_false)

Example

You can copy the following LINQ script and try the above example on the demo.ecommerce.data table. 

from demo.ecommerce.data select statusCode > 400 as statusCode_type, ifthenelse(statusCode_type, "Client error response", "Successful response") as responseType