| Excerpt |
|---|
Comparison operators enable you to compare values in the left-hand side of an expression to the values in the right-hand side of an expression. |
| Code Block |
|---|
(left-hand side) (operator) (right-hand side) |
These evaluations result in a Boolean true or false result and can be used as the basis for determining whether the transformation is executed on the row or column of data. The following operators are supported:
| Operator Name | Symbol | Example Expression | Output | Notes |
|---|
| less than | < | 3 < 6
| true | |
| less than or equal to | <= | 6 <= 5
| false | The following operator generates an error: =< |
| greater than | > | 3 > 6
| false | |
| greater than or equal to | >= | 6 >= 5
| true | The following operator generates an error: => |
| equal to | == | 4 == 4 | true | For this comparison operator, you must use two equals signs, or an error is generated. |
| not equal to | <> or != | 4 <> 4 | false | Both operators are supported. The following operator generates an error: =! |
The above examples apply to integer values only. Below, you can review how the comparison operators apply to different data types.
Usage
Comparison operators are used to determine the condition of a set of data. Typically, they are applied in evaluations of values or rows.
For example, your dataset is the following:
| city |
|---|
| San Francisco |
| Los Angeles |
| Chicago |
| New York |
You could use the following transformation to flag all rows whose city value equals San Francisco:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:(city == 'San Francisco') |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | (city == 'San Francisco') |
|---|
| SearchTerm | New formula |
|---|
|
Your output looks like the following:
| city | column1 |
|---|
| San Francisco | true |
| Los Angeles | false |
| Chicago | false |
| New York | false |
You can optionally combine the above with an IF function, which enables you to write values for true or false outcomes:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'BaseballTeam' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:if(city == 'San Francisco', 'Home of the Giants!', 'Some other team') as:'BaseballTeam' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | if(city == 'San Francisco', 'Home of the Giants!', 'Some other team') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Note that the optional as: clause can be used to rename the generated columns. See Derive Transform.
| city | BaseballTeam |
|---|
| San Francisco | Home of the Giants! |
| Los Angeles | Some other team |
| Chicago | Some other team |
| New York | Some other team |
| Info |
|---|
NOTE: When a comparison is applied to a set of values, the type of data of each source value is re-inferred to match any literal values used on the other side of the expression. This method allows for more powerful comparisons. In the following examples, values taken from the MySource column are re-typed to match the inferred data type of the other side of the comparison. |
Less Than (or Equal To)
| Column Type | Example Transformation | Output | Notes |
|---|
| Integer | | D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:(MySource < 5) |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | (MySource < 5) |
|---|
| SearchTerm | New formula |
|---|
|
| true for all values in MySource that are less than 5.- Otherwise,
false.
| |
| Decimal | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | (MySource <= 2.5) |
|---|
| Type | step |
|---|
| WrangleText | keep row:(MySource <= 2.5) |
|---|
| p01Name | Condition |
|---|
| p01Value | Custom formula |
|---|
| p02Name | Type of formula |
|---|
| p02Value | Custom single |
|---|
| p03Name | Condition |
|---|
| p04Value | Keep matching rows |
|---|
| p04Name | Action |
|---|
| SearchTerm | Filter rows |
|---|
|
| Retains all rows in the dataset where the value in the MySource column is less than or equal to 2.5. | |
| Datetime | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | (Date <= DATE(2009,12,31)) |
|---|
| Type | step |
|---|
| WrangleText | keep row:(Date <= DATE(2009,12,31)) |
|---|
| p01Name | Condition |
|---|
| p01Value | Custom formula |
|---|
| p02Name | Type of formula |
|---|
| p02Value | Custom single |
|---|
| p03Name | Condition |
|---|
| p04Value | Keep matching rows |
|---|
| p04Name | Action |
|---|
| SearchTerm | Filter rows |
|---|
|
| Retains all rows whose Date column value is less than or equal to 12/31/2009. | You can also use the DATEDIF function to generate the number of days difference between two date values. Then, you can compare this difference to another value. See DATEDIF Function. |
| String (and all other data types) | | D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:(LEN(MySource) < 5)) |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | (LEN(MySource) < 5)) |
|---|
| SearchTerm | New formula |
|---|
|
| true for any string value in the MySource column whose length is less than 5 characters.- Otherwise,
false - See LEN Function.
| - For comparison purposes, all data types not previously listed in this table behave like strings.
- Since strings are non-numeric value, a function must be applied to string data to render a comparison.
|
Greater Than (or Equal To)
See previous section.
Equal to
| Column Type | Example Transformation | Output | Notes |
|---|
| Integer | | D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:(MySource == 5) |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | (MySource == 5) |
|---|
| SearchTerm | New formula |
|---|
|
| true for all values in the MySource column that are 5.- Otherwise,
false.
| If the source column contains Decimal values and the right-hand side is an integer value, the Decimal values that are also integers can match in the comparison (e.g. 2.0 == 2). |
| Decimal | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | (MySource == 2.5) |
|---|
| Type | step |
|---|
| WrangleText | keep row:(MySource == 2.5) |
|---|
| p01Name | Condition |
|---|
| p01Value | Custom formula |
|---|
| p02Name | Type of formula |
|---|
| p02Value | Custom single |
|---|
| p03Name | Condition |
|---|
| p04Value | Keep matching rows |
|---|
| p04Name | Action |
|---|
| SearchTerm | Filter rows |
|---|
|
| Retains all rows in the dataset where the value in the MySource column is exactly 2.5. | If the source column contains integers and the right-hand side is a Decimal type value, integer values are rounded for comparison. |
| Datetime | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | (Date == DATE(2016,12,25)) |
|---|
| Type | step |
|---|
| WrangleText | keep row:(Date == DATE(2016,12,25)) |
|---|
| p01Name | Condition |
|---|
| p01Value | Custom formula |
|---|
| p02Name | Type of formula |
|---|
| p02Value | Custom single |
|---|
| p03Name | Condition |
|---|
| p04Value | Keep matching rows |
|---|
| p04Name | Action |
|---|
| SearchTerm | Filter rows |
|---|
|
| Retains all rows whose Date column value is equal to 12/25/2016 . | |
| String (and all other data types) | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | (LEN(MySource) == 5)) |
|---|
| Type | step |
|---|
| WrangleText | keep row:(LEN(MySource) == 5)) |
|---|
| p01Name | Condition |
|---|
| p01Value | Custom formula |
|---|
| p02Name | Type of formula |
|---|
| p02Value | Custom single |
|---|
| p03Name | Condition |
|---|
| p04Value | Keep matching rows |
|---|
| p04Name | Action |
|---|
| SearchTerm | Filter rows |
|---|
|
| Retains all rows in the dataset where the length of the string value in the MySource column is 5 characters. | - For comparison purposes, all data types not previously listed in this table behave like strings.
- Since strings are non-numeric value, a function must be applied to string data to render a comparison.
|
Not Equal to
| Column Type | Example Transformation | Output | Notes |
|---|
| Integer | | D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value:(MySource <> 5) |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | (MySource <> 5) |
|---|
| SearchTerm | New formula |
|---|
|
| true for all values in the MySource column that are not 5.- Otherwise,
false.
| If the source column contains Decimal values and the right-hand side is an integer value, the Decimal values that are also integers can match in the comparison (e.g. 2.0 == 2 ). |
| Decimal | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | (MySource <> 2.5) |
|---|
| Type | step |
|---|
| WrangleText | keep row:(MySource <> 2.5) |
|---|
| p01Name | Condition |
|---|
| p01Value | Custom formula |
|---|
| p02Name | Type of formula |
|---|
| p02Value | Custom single |
|---|
| p03Name | Condition |
|---|
| p04Value | Keep matching rows |
|---|
| p04Name | Action |
|---|
| SearchTerm | Filter rows |
|---|
|
| Retains all rows in the dataset where the value in the MySource column is not 2.5. | If the source column contains integers and the right-hand side is a Decimal type value, integer values are rounded for comparison. |
| Datetime | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | (Date <> DATE(2016,4,15)) |
|---|
| Type | step |
|---|
| WrangleText | keep row:(Date <> DATE(2016,4,15)) |
|---|
| p01Name | Condition |
|---|
| p01Value | Custom formula |
|---|
| p02Name | Type of formula |
|---|
| p02Value | Custom single |
|---|
| p03Name | Condition |
|---|
| p04Value | Keep matching rows |
|---|
| p04Name | Action |
|---|
| SearchTerm | Filter rows |
|---|
|
| Retains all rows in the dataset where the Date value does not equal 4/15/2016. | |
| String (and all other data types) | | D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | (LEN(MySource) <> 5)) |
|---|
| Type | step |
|---|
| WrangleText | keep row:(LEN(MySource) <> 5)) |
|---|
| p01Name | Condition |
|---|
| p01Value | Custom formula |
|---|
| p02Name | Type of formula |
|---|
| p02Value | Custom single |
|---|
| p03Name | Condition |
|---|
| p04Value | Keep matching rows |
|---|
| p04Name | Action |
|---|
| SearchTerm | Filter rows |
|---|
|
| Retains all rows in the dataset where the length of the string value in the MySource column is not 5 characters. | - For comparison purposes, all data types not previously listed in this table behave like strings.
- Since strings are non-numeric value, a function must be applied to string data to render a comparison.
|
