Text Functions

• LEFT([text field ], n ), returns n characters from the left side of a text field . If n is omitted, one character is returned.
• RIGHT([text field ], n ), returns n characters from the right side of a text field . If n is omitted, one character is returned.
• CONCATENATE([text field1 ], [text field2 ], [text field3 ], ...), returns the combination of text field1 and text field2 (and optionally additional text fields) as one longer text string.
• MID([text field ], start_num, num_chars), returns the characters from the middle of a text string, given a starting position and length.
• PROPER([text field ]), converts and returns a text string to proper case; the first letter in each word to uppercase, and all other letters to lowercase.
• LEN([text field ]), returns the number of characters in the specific text field.
• TRIM([text field ]), returns the text field with any trailing white space, such as blank spaces, removed.
• UPPER([text field ]), returns text as an uppercase. This function is applicable only for Text type and ConstantText type only.
• VALUE([text field ]), converts a text string representing a number into the number (double) format. It supports percentages, currencies, scientific notations, and negative numbers.
• LOWER([text field ]), returns text as a lowercase. This function is applicable only for Text type and ConstantText type only.
• FIND([text field1 ], [text field2 ]), returns the starting position of text field1 within text field2. You can also use quotes to specify the text to find in text field2. If the text appears more than once within text field2 , this function returns the starting position of the first occurrence, from left to right.
• LOOKUP("ESM ModelName ", [ReturnFieldName ], {Key Field Name }), returns the value of ReturnFieldName in ESM ModelName that corresponds to the row in ESM ModelName where Key Field Name was found. Key Field Name must exist in ESM ModelName and in the model that contains the LOOKUP function. The Key Field Name that exists in both models must be enclosed with curly braces { }. See Example: Using the LOOKUP Function. To look up a number instead of text, see RANGELOOKUP under Math and Numeric Functions.
• TEXT([date ], format ), returns the date as a text string in the specified format . The argument date must be specified by using a date field, the DATE function, or the result of other formulas. The argument format is the same as the Format column for Date fields and must use uppercase: "MMM-YYYY", "MMM-YY", "DD/MM/YYYY", "MM/DD/YYYY", "DD-MM-YYYY", or "MM-DD-YYYY". Both arguments are required.

Example: TEXT(DATE(1,1,2019), "MMM-YYYY") returns the text string "Jan-2019".

See Example: Using EOMonth, EDate, Min, Max, and Text Functions

Example: Using the LOOKUP Function

The LOOKUP function allows two ESM models to communicate with each other. There must be one matching field in both models, called the Key Field.

In the following example, Account is the Key Field. Models AA1 and BB1 will interact. Model AA1 contains the information that model BB1 will look up.

Here is the list of fields in model AA1.

Here is model AA1's data.

Model BB1 will look up data in model AA1 based on the common key field, Account.

Here is the list of fields in model BB1. Three fields are formulas with LOOKUP functions.

To calculate the LOOKUP functions, you need to load data only into the first field, Account. All other fields are derived.

Copy and paste the accounts into column A, then click Load Data.

Click Refresh and then you will see the remaining columns calculated and filled in.

Explanation

• Reminder: here is the syntax for the LOOKUP function:

LOOKUP("ESM ModelName ", [ReturnFieldName ], {Key Field Name })

• Model BB1 has 4 fields.
  • The first field in BB1 is a text field that serves as the Key Field: Account.
  • The second field in BB1 is Offset Account and contains the following formula:
    LOOKUP("AA1","AAOffset Account", {Account})
    Offset Account is derived by matching the Key Field {Account} (from row 1 in BB1, "PM Fee Revenue"), to a field of the same name in model AA1. Once the matching row is found, the value of the field called AAOffset Account from the row where "PM Fee Revenue" was found in AA1 is returned and placed into Field 2 in BB1: Offset Account.
  • The third field in BB1 is Activity and contains the following formula:
    LOOKUP("AA1","AAActivity", {Account})
    Activity is derived by looking up the {Account} value (still in row 1 of BB1, "PM Fee Revenue"), in model AA1, and placing the value of AAActivity from AA1 into Field 3 in BB1: Activity.
  • The fourth field in BB1 is Offset Activity and contains the following formula:
    LOOKUP("AA1","AAOffset Activity", {Account})
    Offset Activity is derived by looking up the {Account} value (still in row 1 of BB1, "PM Fee Revenue"), in model AA1, and placing the value of AAOffset Activity from AA1 into Field 4 in BB1: Offset Activity.
• This completes the first row in BB1.
• In the second and following rows of BB1, the same method is used to look up the Key Field and derive the values to place into Fields 2-4.
• When a row is encountered in BB1 where the Key Field does not exist in model AA1 ("DEFABC"), no values for Fields 2-4 are created.