Contents:
Comparison operators enable you to compare values in the lefthand side of an expression to the values in the righthand side of an expression.
(lefthand side) (operator) (righthand side)
These evaluations result in a Boolean true
or false
result and can be used as the basis for determining whether the action of transform is executed on the row or column of data. The following operators are supported:
Operator Name  Symbol  Example Expression  Output  Notes 

less than  < 
 true  
less than or equal to  <= 
 false  The following operator generates an error: =< 
greater than  > 
 false  
greater than or equal to  >= 
 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 
!=  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 transform to flag all rows whose city
value equals San Francisco
:
derive type:single value:(city == 'San Francisco')
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:
derive type:single value:IF(city == 'San Francisco', 'Home of the Giants!', 'Some other team') as:'BaseballTeam'
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 
Examples
Tip: For additional examples, see Common Tasks.
NOTE: When a comparison is applied to a set of values, the type of data of each source value is reinferred 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 retyped to match the inferred data type of the other side of the comparison.
Less Than (or Equal To)
Column Type  Example Transform  Output  Notes 

Integer  derive type:single value:(MySource < 5) 
 
Decimal  keep row:(MySource <= 2.5)  Retains all rows in the dataset where the value in the MySource column is less than or equal to 2.5.  
Datetime  keep row:(Date <= DATE(2009,12,31))  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)  derive type:single value:(LEN(MySource) < 5)) 


Greater Than (or Equal To)
See previous section.
Equal to
Column Type  Example Transform  Output  Notes 

Integer  derive type:single value:(MySource == 5) 
 If the source column contains Decimal values and the righthand side is an integer value, the Decimal values that are also integers can match in the comparison (e.g. 2.0 == 2 ). 
Decimal  keep row:(MySource == 2.5)  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 righthand side is a Decimal type value, integer values are rounded for comparison. 
Datetime  keep row:(Date == DATE(2016,12,25))  Retains all rows whose Date column value is equal to 12/25/2016 .  
String (and all other data types)  keep row:(LEN(MySource) == 5))  Retains all rows in the dataset where the length of the string value in the MySource column is 5 characters. 

Not Equal to
Column Type  Example Transform  Output  Notes 

Integer  derive type:single value:(MySource <> 5) 
 If the source column contains Decimal values and the righthand side is an integer value, the Decimal values that are also integers can match in the comparison (e.g. 2.0 == 2 ). 
Decimal  keep row:(MySource <> 2.5)  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 righthand side is a Decimal type value, integer values are rounded for comparison. 
Datetime  keep row:(Date <> DATE(2016,4,15))  Retains all rows in the dataset where the Date value does not equal 4/15/2016.  
String (and all other data types)  keep row:(LEN(MySource) <> 5))  Retains all rows in the dataset where the length of the string value in the MySource column is not 5 characters. 

This page has no comments.