Creating Expressions

An expression is a formula that returns a value, and is used to define calculated fields in the Prepare Window.  Expressions can include:

• columns from the table

• constants

• operators (arithmetical, logical, comparison and concatenation)

• functions

These are examples of expressions:

• Amount x .10

• If(Customer="Ace Resellers",Unit_price*.55, Unit_price*.65)

• CharToSeconds(End_time)-CharToSeconds(Start_time)<60

Succeeding sections describe rules for defining expressions.

Monarch Data Prep Studio will highlight the Expression Builder with a red border if it finds errors in your expression:

 

In most cases, this means that your expression violates one of the rules described below.

Using field names

A column name may be used anywhere in an expression where the field’s values would be appropriate, such as City="Boston", which compares the values found in the City column to the string "Boston".

Note: When typing a column name into an expression, you must enclose the column name in square brackets ([ ]) if the column name include spaces or punctuation characters, does not begin with a letter (a – z, A – Z). If you prefer not to remember this rule when entering column names in expressions, just select the column name from the columns list box. Monarch Data Prep Studio determines whether the square brackets are required and adds them if necessary. Monarch Data Prep Studio will also update your expressions automatically if you subsequently change a column name that has been included in an expression.

Using constants

A constant is a number or string that does not change in value The following syntax rules apply to the use of constants:

• Character strings must be enclosed in double quotes, as in "Boston".

• Date values must be enclosed in braces as in {June 17, 2001} or {03/28/98}. Dates must fall in the range between January 01, 1900 and December 31, 2400.

• Numbers must contain no more than 15 significant digits, excluding the optional negation sign and decimal point, as in -1234567890.12345. Numbers are entered without quotes or braces.
  

Using mathematical operators

An mathematical operator is used to perform calculations on numeric values. Monarch Data Prep Studio expressions support the following arithmetical operators:

String functions operate on or with character strings.

Operator	Description
+	addition
-	subtraction
*	multiplication
/	division

 

Exponents are indicated with either ^ or **. The expression MASS^3 (or MASS**3) returns the cube of the value found in the MASS field.

Using comparison operators

A comparison operator is used to compare two values. Monarch Data Prep Studio expressions support the following comparison operators:

Operator	Description
=	equal to
<>	not equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to

 

Note that asterisk (*) and question mark (?) characters are interpreted as wildcards when comparing strings during expression evaluation"

For example:

"Donna *" = "Donna Smith"

"Do??a" = "Donna" and "Do??a" = "Dotta"

If it is necessary to look for a literal asterisk in a string, you can do the following::

• Use the Asc() function to directly compare character code values.

For example:

If (Asc(DateLeft)=Asc("*"),"Arrival", if (DateLeft=" ","Close Out","Departure"))

• Use the Instr() function to search for a literal asterisk in the target string, as follows:

For example:

if (Instr("*",DateLeft) > 0,"Arrival", if (DateLeft=" ","Close Out","Departure"))

• Create a column called "Asterisk" containing a single asterisk character.  Then refer to this column in your expression, as follows:

if (DateLeft=Asterisk,"Arrival", if (DateLeft=" ","Close Out","Departure"))

Using string operators

The string operators (+ and -) are used to concatenate (i.e., to combine) character strings. The plus sign (+) concatenates strings. The minus sign (-) first removes any trailing spaces from the first string, then concatenates the strings. The plus sign (+) is used more often since it produces a more intuitive expression. For example, to create a Fullname field from a Firstname field and a Lastname field, use the expression:

Firstname+" "+Lastname

Firstname+" " concatenates one space to separate the first name from the last name, then +Lastname appends the Lastname field.

In the expression Firstname-" "+Lastname the minus sign is used to right trim the Firstname field before concatenating the Lastname field.

Using logical operators

Logical operators are used to combine comparisons. Each of the logical operators is described below:

Operator	Description
.And.	Requires both halves of an expression to be TRUE.
.Or.	Requires either half of an expression to be TRUE.
.Not.	Requires an expression to be FALSE.
.In.()	Determines whether a value is present within a list of values.
.NotIn.()	Determines whether a value is not present within a list of values.

 

Handling invalid values

When a filter expression references a field that contains one or more invalid values, the records that contain the invalid values are ignored (i.e., will not be returned by the filter). A field value is considered invalid if it is a calculated field value for which division by zero occurs or it is a date field value that contains a blank (an invalid date).

Inserting comments

Monarch Data Prep Studio allows comments in expressions. Comments must be inserted between the comments brackets /* */. You may place comments anywhere in an expression as long as they do not interrupt a field name or value. For example, the following expressions are valid:

• Unit_price*Quantity/*Calculates transaction totals*/

• Unit_price*/*Calculates transaction totals*/Quantity

However, the following expression is not valid because the comment interrupts the Quantity field name:

• Unit_price*Quan/*Calculates transaction totals*/tity

Nested comparisons

Monarch Data Prep Studio allows any combination of logical operators in an expression. However, you must use parentheses where appropriate to achieve the desired result. In Monarch Data Prep Studio you are allowed unlimited nested pairs of parentheses.

Using wildcards

A wildcard may be used to represent any character or any series of characters in a text string. Monarch Data Prep Studio expressions support two wildcard characters, the question mark (?) and asterisk (*). Use a question mark to indicate "any single character". Use an asterisk to indicate "any series of characters that follow".

Case sensitivity

Comparisons performed within expressions are not case-sensitive. For example, the expression

If(Customer="Ace Resellers",Unit_price*.55, Unit_price*.65)

computes a discount rate for a preferred customer (Ace Resellers) and a higher rate for all other customers. This expression will compute the discount rate for "Ace Resellers" when the Customer column contains any of the following values:

• ace resellers

• Ace Resellers

• ACE RESELLERS

Using functions

Monarch Data Prep Studio expressions support a variety of functions. Most functions require one or more arguments and all return either a character string, a number, or a date. Monarch Data Prep Studio provides help below the expression builder by showing you the syntax of a function you have selected.

The following syntax rules apply to all functions:

• A function name may be entered in uppercase or lowercase letters.

• Function arguments must be enclosed in parentheses. If multiple arguments are required, they must be separated by commas and must appear in the order specified by the function definition.

• A function may be embedded as an argument within another function.

 

Related Links

• Functions Reference

• Function Syntax Rules