If

<< Click to Display Table of Contents >>

Navigation:  Reference > Transforms >

If

Description

Sets the value of a new column based conditionally on values in one or more other columns.

 

Examples

Add a new column based conditionally on 'Payment' and 'Currency' columns:

 

how to perform conditional/if evaluation of a column example

 

If the h1 value is 1 use the value of the h2 column, otherwise use the value of h3 column:

 

if-example-2

 

Inputs

One.

 

Options

Click the add-button@1xELSE IF button to add a new IF/ELSE IF..THEN condition.

Click the add-button@1xAND'button to add an AND to the selected IF/ELSE IF..THEN.

Click the remove-button@2x button to delete the selected IF/ELSE IF..THEN/AND.

The Logic column shows the type of row.

Set Column to the column you wish to match.

Set Op. to the comparison operator.

Set Value to the value you wish to compare.

Check case sensitive to use case sensitive matching for text.

 

Notes

The IF, THEN and ELSE values can use column variables, as in the second example above.

You can simulate OR with multiple IF statements. For example:

 

 IF x = 1 OR y = 2 

    THEN 3

 

  Is equivalent to:

 

 IF x = 1

    THEN 3

 ELSE IF y = 2

    THEN 3

 

Number, date and text values are treated differently for Equal to, Greater than, Less than, Greater than equal, Less than equal and Not equal to operations. Any values that can be converted to a number will be treated as a number. Any values that match the supported date formats in Preferences will be treated as a date.

All values are treated as text for Contains, Starts with, Ends with, Matches regex, Is not, Doesn't start with, Doesn't end with and Doesn't match regex operations.

Comparisons of text are whitespace sensitive. Cells with whitespace will not match Is empty. You can use Whitespace to remove whitespace before filtering and Replace to get of other unwanted characters (e.g. whitespace inside the text).

See here for more details on Regular expressions (regex).

 

See also

Lookup