<< Click to Display Table of Contents >>

Navigation:  Reference > Transforms >



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



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


how to perform conditional/if evaluation of a column example


If the 'h1' and 'h2' columns have the same value then use the value of the 'h2' column, otherwise use the value of 'h3' column (using column variables).:








Set New column name to the name of the new column you want to create.

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.



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.

oIf both values are numeric, a numeric comparison will be carried out. This is accurate to approximately 16 digits of precision.

oIf both values match a supported date formats in Preferences, a date comparison will be carried out.

oOtherwise the values will be treated as text.

oFor example, an empty value is considered less than 0, because they will be compared as text. So you might want to replace empty values with 0 or remove those rows with Filter, before comparing them.

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