Expression Builder Operators - trillium_discovery - trillium_quality - 17.1

Sum of

A conjunction between two statements. Both statements must be true for the conjunction to be true.

Syntax

attribute1 = value1 AND attribute2 = value2

where

attribute1 = value1 is the first statement to be joined.

attribute2 = value2 is the second statement to be joined.

Note the following guidelines:

• AND can also be used with the OR and NOT operators.
• Expressions are evaluated from left to right. Precedence is implied by the order shown in the expression, although parenthetical statements always take priority.
• Any comparison operator can be used to join statements.
• Statements are not case-sensitive.

Example

• The following associates the values tomato and fruit to attributes Name and Type: Name = "tomato" AND Type = "fruit"

Logical condition operators. The ELSE statement is optional. When using the Expression Builder inside a Quality project, do not select "IF" to specify a condition. The "IF" part of the syntax is automatically generated. 

Syntax

IF expression1 THEN expression2 ELSE expression3 |TRUE|FALSE

where expression is the condition or argument that completes the IF, THEN, ELSE logic. The ELSE statement takes an expression or the keywords "TRUE" or "FALSE".

Example

The following validates the value in the Auth Id Rep attribute:

IF [Status] <> "AUTH" AND [Status] <> "REJECT"

THEN [Auth Id Rep] = "NSP"

ELSE TRUE

Logical condition operators. Syntax is similar to the IF, THEN, ELSE logical statement.

Syntax

IF expression1 THEN expression2 ELSE IF expression3 THEN expression4 ELSE expression5 |TRUE|FALSE

where expression is a condition or argument that completes the IF, THEN, ELSE IF, THEN, ELSE logic. The ELSE statement takes an expression or the keywords "TRUE" or "FALSE".

Example

The following validates the value in the Acc Type attribute:

IF [Acc Type] IN (410)

THEN SUBSTR([Acc Num],4,3) = "410"AND PATTERN(SUBSTR([Acc Num],1,3),"rich") = "d3" AND PATTERN(SUBSTR([Acc Num],7,8),"rich") = "d8"

ELSE IF [Acc Type] IN (510)

THEN SUBSTR([Acc Num],4,3) = "510" AND PATTERN(SUBSTR([Acc Num],1,3),"rich") = "d3" AND PATTERN(SUBSTR([Acc Num],7,8),"rich") = "d8"

ELSE PATTERN([Acc Num], "rich") = "d20"

Reverses the truth value of the statement that follows, resulting in the opposite of the statement it evaluates.

Syntax

NOT attribute1 = value1

where attribute1 = value1 is the statement to be reversed.

Note the following guidelines:

• NOT can also be used with the AND and OR operators.
• Expressions are evaluated from left to right. Precedence is implied by the order shown in the expression, although parenthetical statements always take priority.
• Statements are not case-sensitive.

Examples

• The following reverses (disconnects) the value "JOHN" from the attribute first_name: NOT(first_name="JOHN")
• The following reverses (disconnects) the values tomato and fruit from the attributes Name and Type: NOT Name = "tomato" AND Type = "fruit"

An inclusive disjunction between two statements. At least one statement must be true for the disjunction to be true.

Syntax

attribute1 = value1 OR attribute2 = value2

where

attribute1 = value1 is the first disjunctive statement in the expression.

attribute2 = value2 is the second disjunctive statement.

Note the following guidelines:

• OR can also be used with the AND and NOT operators
• Expressions are evaluated from left to right. Precedence is implied by the order shown in the expression, although parenthetical statements always take priority.
• Any comparison operator can be used to join statements.
• Statements are not case-sensitive.

Examples

• The following returns a correct value if either statement is true. If neither is true, no value is returned: age > 21 OR year_of_birth > 1990
• The following shows that either the value tomato is valid for the Name attribute or the value fruit is valid for the Type attribute: Name = "tomato" OR Type = "fruit"
• The following expression validates that if the Sort Code attribute is populated, then it is correct for the country in the Client attribute: Sort Code = "" OR (PATTERN(Sort Code,"default") LIKE "d9" AND Client LIKE "US*") OR (PATTERN(Sort Code,"default") LIKE "d2pd2pd2" AND Client LIKE "A*")

Tests whether a value is equal to a value in a list.

Syntax

value IN ("value1","value2",..."valueN")

OR

value IN LIST("value1","value2",..."valueN")

OR

value IN "value1","value2",..."valueN"

where

• value is the value to be tested.
• value1,value2,valueN are the values in the set to be tested for a match.

Note: When using the IN operator in Quality, the LIST operator is automatically added to the syntax in the settings output file. You do not have to add LIST to the expression.

Examples

• You can create a filter to return certain values. The statement Name IN ("tomato","pear") results in returning the rows where tomato or pear occur in the Name attribute.
• You can create validation and business rules based on whether a value matches one of a set of values. For example,  IF [apt_num] IN (2??) THEN… ELSE IF [apt_num] IN (3??,4??,5??) THEN… 

Comparison operator that searches for a value that matches a specified pattern.

Syntax

value LIKE "pattern"

where

• value is the attribute to be searched.
• "pattern" is the pattern used for the match.

Note the following guidelines:

• To broaden the match criteria, use the wildcard characters asterisk (*) and question mark (?), as follows:
  • (?) specifies a single character.
  • (*) specifies zero or more consecutive characters in a string.
  • You can use more than one of each wildcard character in an expression; for example, "s*@h*tm*l.*", "*Nelson*", and "*05??2010"
  • If you are searching on an asterisk or a question mark and do not want to use it as a wildcard character, you must escape it using a forward slash (/) as escape character when using it for expressions or a backward slash (\) escape character for profiling (Discovery) expressions. For example, in a expression, to search on "abc*xyz" and "abc?xyz", write the patterns as "abc/*xyz" and "abc/?xyz". To search on a pattern that includes "/*", for example "abc/*xyz", you write the pattern as "abc///*xyz".
• Use square brackets ([ ]) to specify any one group of characters in a set; for example [a,b,c]. Use a dash (-) to specify a range of characters; for example, [1-10].

Examples

• If ATTR1 contains the values test and testing, the expression ATTR1 LIKE "tes?" returns "test". To return both "test" and "testing", use ATTR1 LIKE "tes*".
• If ATTR2 contains the values "stick" and "stack", the expression ATTR2 LIKE "st[a-j]ck" returns both.
• The following shows how to view all values in the NAME attribute that contain the string "cust": NAME LIKE "*cust*".
• To find all Product identification numbers with a 5 in the 3rd position, use the following: Product_Id LIKE "??5*". 

Concatenates multiple arguments (such as strings, attributes, and integers) with no space between each argument.

Syntax

argument1 || argument2 || argument3

where:

• argument1 indicates the first argument in a series of concatenated arguments.
• argument2 indicates the second argument in a series.
• argument3 indicates the third argument, and so on.

Examples

• "One" || ATX || 3 returns OneATX3.
• "Nelson" || "Ruiz" returns "NelsonRuiz".
• House No - 2 || House No - 1 results in 01 for all records.