DC 12.3 | Script Functions G-M

User Guide > Scripting > Script Functions > Script Functions G-M

Was this helpful?

Script Functions G-M

This topic includes information on setting the scope of functions, as well as details on each of the available functions.

GetDirectory Function

Description	Finds the name of the current directory
Return	Name of the current directory or folder
Return (Null)	Not applicable for this function
Syntax	GetDirectory()
Parameters	No parameters are required, however, GetDirectory must be followed by parentheses.
Remarks	This function returns the full path name of the current working directory of the application process. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	MakeDirectory Function and RemoveDirectory Function
Example	FileWrite("The current directory is " & GetDirectory())

GetThreadID Function

Description	Identifies the individual thread running a specific process
Return	Unique string that represents the thread ID
Return (Null)	Not applicable for this function
Syntax	GetThreadID()
Parameters	None.
Remarks	When you run a process using multiple threads, it is sometimes critical to know, within the transformation, which particular thread that transformation is using. This enables you to create and use global objects such as message objects and import objects. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	MakeDirectory Function and RemoveDirectory Function
Example	'Returns 000003c4: GetThreadID()

GetTimeZone Function

Description	Returns the time zone of the specified date and time value, or else returns the local time zone if no value is specified. The time zone is returned as the number of seconds offset from UTC.
Return	See description.
Return (Null)	Error: Invalid use of NULL.
Syntax	GetTimeZone(dateString)
Parameters	• dateString - (optional) An actual date value or an expression that evaluates to a date value. If this parameter is not specified, the result will be the system time zone. Note: If a string date value is specified, it will be converted to a date if possible, but that will always have the system time zone set so it’s an extremely convoluted way of calling getTimeZone without an argument.
Remarks	You can use the DateValMask Function to define the date mask in your expressions.
See Also	DateValMask Function, CDate Function, ChangeTimeZone Function
Example	The following example prints your local time zone. The time zone is printed as the number of seconds offset from UTC. For example “-21600” is printed for MDT (-6 hours). print("Local time zone is " & GetTimezone())

GSub Function

Description	Globally replaces every instance of an expression string in one field of data with a specified string. You may substitute ReplaceAll for GSub, as both are identical in syntax.
Return	Value specified in newstring (overwrites oldstring)
Return (Null)	Error: Incorrect argument for function.
Syntax	GSub(oldstring, newstring, string)
Parameters	This function has the following parameters: • oldstring - The original string that the expression searches for to replace with the newstring. If a string literal, enclose in quotation marks. • newstring - The string that replaces every instance of the oldstring. If a string literal, enclose in quotation marks. • string - Any string containing data. If a string literal, enclose in quotation marks.
Remarks	The return value of this function is the string specified by the fieldname parameter. All sections that match the regular expression specified as the oldstring parameter are replaced by the string specified as the newstring parameter. Be careful to observe case when using a function that performs a string-search. String-search functions are case-sensitive. GSub recognizes all special characters in the oldstring parameter. All special characters must be escaped to be recognized. See Regular Expressions. If you attempt to use this function on very large strings (50K – 100K and above), you may experience a performance hit. To avoid long run times of your transformations, do not use GSub on very large strings. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	InitCaps Function, LCase Function, LTrim Function, StrReplace Function, Sub Function, Transliterate Function, UCase Function
Examples	The '(' and ')' characters are used for grouping in regular expressions. The expression "(abc)" matches one or more occurrences of the string abc. If you want to search for the literal characters "("and ")", use a backslash as an escape character. Below, the special characters "(" and ")" in Field1 are escaped with the "\" symbol, and replaced by an empty string in the target: GSub("$", " ", GSub("$", " ", FieldAt("/SOURCE/R1/Field1"))) Replace "Avenue" with "Ave." in the Address field: GSub("Avenue", "Ave.", FieldAt("Address")) Look for "Mon" or "Fri" in ("Field1") of the source file and replace them with "Monday" and "Friday", respectively. If any value is found in ("Field1") other than "Mon" or "Fri", leave data alone: T = GSub("Mon.", "Monday", FieldAt("/SOURCE/R1/Field1")) GSub("Fri.", "Friday", T) Another way to do the previous example is to nest the two substitutions together: GSub("Mon.", "Monday", GSub("Fri.", "Friday", FieldAt("/SOURCE/R1/FIELD1"))) If you want to delete blank spaces within a character field, you must put two spaces before the asterisk in the first parameter and no space between the quotes in the second parameter. "FIELD" is the field name of the character field in your source file from which you want to delete the blank spaces: GSub(" *", "", FieldAt("/SOURCE/R1/FIELD")) To delete all non alphanumeric characters from a field: GSub("[^A-Za-z0-9n]","", FieldAt("/SOURCE/R1/Field1"))

Hex Function

Description	Prints the hexadecimal representation of a number
Return	String representing the hexadecimal value of a supplied number
Return (Null)	Error: Incorrect argument for function.
Syntax	Hex(number)
Parameters	This function has the following parameters: • number (required) - Any numeric expression: – If number is null, Hex returns null. – If number is empty, Hex returns zero (0). – If number is any other number, Hex returns up to eight octal characters.
Remarks	You can represent hexadecimal numbers directly by preceding numbers in the proper range with &H. For example, &H10 represents decimal 16 in hexadecimal notation. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Oct Function
Examples	Dim MyHex MyHex = Hex(5) 'Returns 5. MyHex = Hex(10) 'Returns A.

Hour Function

Description	Converts clock time to an hour integer
Return	Integer between 0 and 23, inclusive, which represents the hour of the day corresponding to the time provided as an argument.
Return (Null)	Error: Incorrect argument count for function.
Syntax	Hour(dateString) or Hour(TimeString)
Parameters	This function has one of the following parameters: • dateString if containing time value. • TimeString: Any value or expression that can represent a date/time.
Remarks	Numbers to the left of the decimal point in number represent the date; numbers to the right of the decimal point represent the time. Negative numbers represent dates prior to December 30, 1899. If number is null, this function returns a null. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	DatePart Function, Day Function, Minute Function, Month Function, Now Function, Second Function, Weekday Function, Year Function
Examples	Return the hour portion of the system time to the target field (if system time is 1:12:30, this function returns "1" to the target: Hour(FieldAt("/SOURCE/R1/Field1")) Return the hour portion from the source ("Hour"). The TimeValue function defines each time to be time values, while the Hour function defines those values as hours: a = TimeValue(Trim(FieldAt("/SOURCE/R1/Hour"))) Hour(a)

IIf Function

Description	Returns a value based on whether an expression evaluates to true or false
Return	One of two arguments depending on the evaluation of an expression
Return (Null)	Error: Incorrect argument count for function
Syntax	IIf(expr, truepart, falsepart)
Parameters	This function has the following parameters: • expr - Expression you want to evaluate. • truepart - Value or expression returned if expr is True (-1). If a string literal, enclose in quotation marks. • falsepart - Value or expression returned if expr is False (0). If a string literal, enclose in quotation marks.
Remarks	You may use IIf (immediately if) to evaluate an expression and return either of two other values. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Choose Function
Example	In this example, if "Account_No" is greater than 10025 and less than 10090, then insert "Bill", else insert an empty string "": IIf(Val(FieldAt("/SOURCE/R1/Account_No")) > 10025 and Val(FieldAt("/SOURCE/R1/Account No")) < 10090, "Bill", "")

ILAddrow Function

Description	Adds a row in an existing lookup table in resident memory.
Return	Not applicable for this function.
Return (Null)	Error: Incorrect argument count
Syntax	ILAddrow (Name, KeyValue, Field1, Value1, [Field2, Value2, ...])
Parameters	This function has the following parameters: • Name (required) - Name of the existing incore lookup table. If a string literal, enclose in quotation marks. • KeyValue (required) - Key value to add to the lookup table. If a string literal, enclose in quotation marks. • Field1 (required) - Result field name in existing lookup table. If a string literal, enclose in quotation marks. • Value1 (required) - Value of the first result field for the row being added. If a string literal, enclose in quotation marks. • Field2 (optional) - Additional result field names. If a string literal, enclose in quotation marks. • Value2 (optional, though required if using Field2) - Additional result field values. If a string literal, enclose in quotation marks.
Remarks	Multiple lookups can be performed with this function. The number of fields for lookup is not limited. Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message.
See Also	ILookup Function, ILBuild Function, ILClear Function
Example	ILAddrow ("MyIncoreTable", "Jon123", "Name", "Jones", "ZIP_Code", "12345")

ILBuild Function

Description	Builds an incore lookup table in resident memory
Return	Not applicable for this function.
Return (Null)	Error: Incorrect argument count
Syntax	ILBuild(Name, Connector.self, [SQL Statement], KeyField, Field1, [Field2, ...])
Parameters	This function has the following parameters: • name (required) - Name of the incore lookup table to build. If a string literal, enclose in quotation marks. • Connector.self (required) - Connector property that returns an object reference (source/target/DJImport).Without this reference, a string is passed containing the type name of the Connector instead of the import object you instantiated. • SQL Statement (optional) - SQL/query language statement used to build the lookup table. If a string literal, enclose in quotation marks. If not used, substitute an empty string (""). Note: Can only be used with SQL-aware connectors. • KeyField (optional) - Key field used for lookups. If a string literal, enclose in quotation marks. • Field1 - Result field. If a string literal, enclose in quotation marks. • Field2 (optional) - Additional result fields. Can continue with more fields. If a string literal, enclose in quotation marks. Caution! The KeyField, Field1, and Field2 parameter names must match the names used in the SQL Statement. Otherwise, you may receive a type mismatch error at run time.
Remarks	Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message.
See Also	ILBuild Function, ILookup Function, ILClear Function, ILNextKey Function
Example	This example builds an incore lookup table and uses a SQL Statement to query the table. This example creates a lookup table called ILTest consisting of the Account No, Company, City, State, and ZIP fields from tutor1.asc (named ANO, ANAME in the table): ' Declare an import object, instantiate it as a SQL Server Connector in a BeforeTransformation event: Dim lConnector as djimport Set lConnector = new DJImport("SQL Server") ' Type the connect string to the SQL Server database: lConnector.connectstring="database=pubs;username=sa" ' Clear the lookup table (to create or replace the table in multiple transformation runs): ILClear(ILTest) ' Build a table based upon a SQL query statement: ILBuild(ILTest, lConnector.self, select ANO, ANAME from LOOKUP, ANO, ANAME) ' Next, include this expression in a Target Field Expression cell to do the actual lookup: ILookup(ILTest, ANAME, Fields("ANO")) ' Destroy the Connector instance: Set lConnector = nothing

ILClear Function

Description	Clears a lookup table in resident memory.
Return	Not applicable for this function.
Return (Null)	Error: Incorrect argument count
Syntax	ILClear(Name)
Parameters	This function has the following parameters: • name (required) - Name of the incore lookup table to clear. If a string literal, enclose in quotation marks
Remarks	Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message. Tip... Use this function to clear an incore table as a precaution in case the table was previously loaded into memory during the process run.
See Also	ILAddrow Function, ILBuild Function, ILookup Function
Example	ILClear("MyLookup")

ILFirstKey Function

Description	Positions the cursor at the first key of an incore lookup table.
Return	Key values of an incore table.
Return (Null)	Error: Incorrect argument count
Syntax	ILFirstKey(Name)
Parameters	This function has the following parameters: • Name (required) - Name of the incore lookup table to step through. If a string literal, enclose in quotation marks.
Remarks	This function is used in conjunction with the ILNextKey Function. Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	ILNextKey Function, ILAddrow Function, ILBuild Function, ILClear Function, ILookup Function, ILRemoveRow Function
Example	Create a local variable called key. Build an incore lookup table called test. Step through test using this function and ILNextKey: Dim key ILClear("test") ILAddrow("test", 1, "field1", A) ILAddrow("test", 2, "field1", B) ILAddrow("test", 3, "field1", C) ILAddrow("test", 4, "field1", D) ILAddrow("test", 5, "field1", E) key = ILFirstKey("test") While Not IsNull(key) MsgBox("Key: " & key) key = ILNextkey("test") Wend

ILNextKey Function

Description	Allows stepping through the key values in an incore lookup table.
Return	Key values of an incore table.
Return (Null)	Error: Incorrect argument count
Syntax	ILNextKey(Name)
Parameters	This function has the following parameters: • Name (required) - Name of the incore lookup table to step through. If a string literal, enclose in quotation marks.
Remarks	This function is used in conjunction with the ILFirstKey Function. Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	ILNextKey Function, ILAddrow Function, ILBuild Function, ILClear Function, ILookup Function, ILRemoveRow Function
Example	Create a local variable called key. Build an incore lookup table called test. Step through test using this function and ILNextKey: Dim key ILClear("test") ILAddrow("test", 1, "field1", A) ILAddrow("test", 2, "field1", B) ILAddrow("test", 3, "field1", C) ILAddrow("test", 4, "field1", D) ILAddrow("test", 5, "field1", E) key = ILFirstKey("test") While Not IsNull(key) MsgBox("Key: " & key) key = ILNextkey("test") Wend

ILookup Function

Description	Queries a lookup file in resident memory.
Return	Value of a field on a key match.
Return (Null)	Error: Incorrect argument count
Syntax	ILookup (Name, FieldName, KeyValue, [DefaultValue])
Parameters	This function has the following parameters: • Name (required) - Name of the incore lookup file to query. If a string literal, enclose in quotation marks. • FieldName (required) - Result field to return. If a string is literal, enclose in quotation marks. • KeyValue (required) - Key value to find in lookup file. • DefaultValue (optional) - Default value to return if the key value is not found (if not specified, null is returned).
Remarks	Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message. Caution! • When you use ILookup in conjunction with ILNextKey, be aware that calling ILookup causes the current key position to point to the key value used in the ILookup call (if the key value exists). If the key value does not exist, the current key position moves to the first key of the incore table. • This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	ILAddrow Function, ILBuild Function, ILClear Function
Example	Place this expression in the appropriate target field expression: ILookup("iltest", "ANAME", FieldAt("/SOURCE/R1/Name"))

ILRemoveRow Function

Description	Removes a row from an existing lookup table in resident memory.
Return	Not applicable for this function.
Return (Null)	Error: Incorrect argument count
Syntax	ILRemoveRow(Name, KeyValue)
Parameters	This function has the following parameters: • Name (required) - Name of the incore lookup table to be removed. If a string literal, enclose in quotation marks. • KeyValue (required) - The key value identifies the row to be removed. If a string literal, enclose in quotation marks.
Remarks	Parameter Limit: Function parameters are passed on the stack. If the stack is empty, the number of parameters that can be passed as arguments has a theoretical maximum of 64, but if other processes, especially iterative structures such as For loops, are also using memory stack resources, then this maximum is reduced. If stack resources are insufficient, you may receive a stack overflow error message.
See Also	ILookup Function, ILBuild Function, ILClear Function, ILAddrow Function
Example	ILRemoveRow ("MyIncoreTable", "Jon123")

Increment Function

Description	Returns the next serial value. Note: The Serial and Increment functions are interchangeable and use the same syntax.
Return	Next serial value of a numeric expression
Return (Null)	Error: Incorrect argument count
Syntax	Increment(number)
Parameters	This function has the following parameter: • number - Any numeric expression. If number is provided, it sets the starting point.
Remarks	The Increment function returns the next serial value. If number is provided, it sets the starting serial number. Increment returns the next serial value. This means number must be entered as the required starting value less one. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Serial Function
Example	The following example writes an incremental number in a field for every record, starting with the number 1: incremental(0) The following example writes an incremental number in a field for every record, starting with the number 223: incremental(222)

InitCaps Function

Description	Converts the first character of each word to uppercase. If the word resembles a name, a recognized abbreviation, or contains a hyphen, InitCaps applies additional capitalization. See Remarks for further information.
Return	Returns the modified string.
Return (Null)	Error: Incorrect argument count for function.
Syntax	InitCaps(string)
Parameters	This function has the following parameters: • string - Any valid string expression. If a string literal, enclose in quotation marks.
Remarks	Data in the string may be all lowercase, all uppercase, or any combination of the two. InitCaps applies additional capitalization under specialized conditions, as follows: • Certain titles and abbreviations are always capitalized this way: RR, PO, PhD, MD • If the string starts with "mc" and the third letter is a consonant, it is converted to McX. Example: McKone • If the string starts with "mac", "van" or "von" and the fourth character is a consonant, it is converted to MacX, VanX or VonX. The only exception to this behavior is "Vancouver." Example: VanDyke • If the string starts with a word followed by a hyphen, the second word is capitalized. Example: Martin-Lewis • If the string starts with a capital letter followed by an apostrophe, the second letter is capitalized. Example: O'Toole Because of these special cases, certain words may be capitalized inappropriately by the InitCaps function. For example, "machine" and "vantage" become "MacHine" and "VanTage."
See Also	LCase Function, UCase Function
Example	The following expression would be used to convert the first character of the names in a field in the source called "NAME" to uppercase (with the remainder in lowercase) in the target file: InitCaps(FieldAt("/SOURCE/R1/NAME")

InputBox Function

Description	Displays a dialog with an editable text box and a prompt. The text box contains a default value. You must click OK or Cancel to continue. If OK is clicked, this function returns the value that was provided in the text box. If Cancel is clicked, the function returns a zero-length string. You can press Tab to select OK, and then press Enter or the space key.
Return	Dialog input value
Return (Null)	Error: Too few arguments for function
Syntax	InputBox(prompt [, [title] [, [default] ])
Parameters	InputBox has these parameters: • prompt: The string expression displayed as the message in the dialog. The maximum length of prompt is approximately 255 characters, depending on the width of each character used in the string. If the prompt is required to be more than one line, you must include a carriage return (ANSI 13) and a line feed (ANSI 10) between each line. If a string literal, enclose in quotation marks. InputBox(“prompt”). • title - The string expression displayed in the title bar of the dialog. If you exclude the title, the title bar is blank. If a string literal, enclose in quotation marks. InputBox(“title”).strexpr2 (required) - String expression being searched. If a string literal, enclose in quotation marks. • default - The string expression displayed in the text box as the default response, if no other input is provided. If you exclude default, the text box is displayed empty.
Remark	InputBox returns a String. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	MsgBox Function
Example	This expression includes a prompt for user information and a dialog title. For each converted record, this expression causes an InputBox dialog to pop up: Global A If A = "" Then b = InputBox("Data Entry") a=1 b Else b End If

InStr Function

Description	Find the position of the first occurrence of one string within another string. You may use Search instead of InStr, as both have the same syntax.
Return	Position of the first occurrence of one string within another string
Return (Null)	Error: Too few arguments for function.
Syntax 1	InStr([start,] strexpr1, strexpr2)
Syntax 2	InStr(start, strexpr1, strexpr2, compare)
Parameters	This function has the following parameters: • start (required with the compare parameter, otherwise optional) - Numeric expression that sets the starting position for each search; start must be between 1 and approximately 65,500. If start is omitted, the search of strexpr1 begins at the first character position. The start argument is not optional if the compare argument is specified. • strexpr1 (required) - String expression being searched. If a string literal, enclose in quotation marks. • strexpr2 (required) - String expression being sought. If a string literal, enclose in quotation marks. • compare (optional) - Specifies the string-comparison method. The argument compare must be 0 or 1. If compare is 0 or not provided, string comparison is case-sensitive. If compare is 1, string comparison is not case-sensitive.
Remarks	If strexpr2 is found within strexpr1, InStr returns the position at which the match was found. If strexpr2 is zero-length, start is returned. If start is greater than strexpr1, strexpr1 is zero-length, or strexpr2 can't be found, InStr returns 0. If either string expression is null, the function returns a null. If start or compare is null, an error occurs. Be careful to observe case when using a function that performs a string-search. String-search Functions are case-sensitive, literal searches. A function that searches for "x" disregards "X". If the function has an optional argument compare, and you do not use the optional compare argument, the expression is case-sensitive by default. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	InStrRev Function
Example	This example illustrates how you can find the position of a dollar sign ($) within source field "Field1". It begins the search in the first byte of data in "Field1" and the search is not case sensitive: Instr(1, FieldAt("/SOURCE/R1/Field1"), "$", 1) Applied to a field containing: Sale price $14.00 Returns: 12

InStrRev Function

Description	Find the position of an occurrence of one string within another, from the end of string
Return	Position of the first occurrence of one string within another string
Return (Null)	Error: Too few arguments for function
Syntax	InstrRev(stringcheck, stringmatch [,start][,compare])
Parameters	This function has the following parameters: • stringcheck - String expression being searched. If a string literal, enclose in quotation marks. • stringmatch - String expression being searched for. If a string literal, enclose in quotation marks. • start - Numeric expression that sets the starting position for each search. If omitted, -1 is used, which means that the search begins at the last character position. If start contains null, an error occurs. • compare - Numeric value indicating the kind of comparison to use when evaluating substrings. If omitted, a binary comparison is performed. A value of 0 performs a binary comparison (default), while a value of 1 performs a text comparison. Return Values • If stringcheck is zero-length, InStrRev returns 0 • If stringcheck is null, InStrRev returns Null • If stringmatch is zero-length, InStrRev returns start • If stringmatch is null, InStrRev returns Null • If stringmatch is not found, InStrRev returns 0 • If stringmatch is found within stringcheck, InStrRev returns Position at which match is found. • If start > Len(stringmatch), InStrRev returns 0
Remarks	This function resembles the InStr function, but it starts at the end of the string and moves backward. Remember case when using a function that performs a string-search. String-search functions are case-sensitive, literal searches. A function that searches for "x" disregards "X". If the function has an optional argument "compare", and you do not use the optional compare argument, the expression is case-sensitive by default. This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	InputBox Function
Example	This example illustrates how you can find the position of a dollar sign ($) within a source field "Field1". It begins the search in the last byte of data (-1) in "Field1". The search is not case-sensitive: InStrRev(FieldAt("/SOURCE/R1/Field1"), "$", -1) Applied to a field containing: Sale price $14.00 Returns: 12

Int Function

Description	Extract the integer of a number
Return	Integer portion of a number
Return (Null)	Error: Incorrect argument count for function.
Syntax	Int(number)
Parameters	This function has the following parameters: • number - Any valid numeric expression.
Remarks	Int removes the fractional part of number and returns the resulting integer value. The data type of the return value is the same as that of the number parameter. If the numeric expression results in a null, Int returns a null. If number is negative, Int returns the first negative integer less than or equal to number. The Int and Fix functions behave identically unless you are working with negative numbers. The Int function rounds to the lower negative integer, while the Fix function rounds to the higher one. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Abs Function, Fix Function, Sgn Function, CInt Function, and CLng Function
Example	The following example returns -99: Int(-98.6)

IsArray Function

Description	Test a variable to determine if it is an array
Return	Boolean value indicating whether a variable is an array
Return (Null)	Error: Incorrect argument count for function.
Syntax	IsArray(identifier)
Parameters	This function has the following parameters: • identifier (required) - Specifies a variable to test.
Remarks	IsArray returns True (-1) if the variable is an array; otherwise, it returns False (0). Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
Example	The following expression returns –1 if VarX is an array, and returns 0 if VarX is not an array: IsArray(VarX)

IsDate Function

Description	Test a value to determine if it can be converted into a date type
Return	Value indicating whether or not another value can be converted to a date
Return (Null)	Error: Incorrect argument count for function.
Syntax	IsDate(value)
Parameters	This function has the following parameters: • value - Expression to be tested. If a string literal, enclose in quotation marks.
Remarks	Returns True (-1) if the expression is a valid date or can be converted one; otherwise, it returns False (0). The range of valid dates is January 1, 1 A.D., through December 31, 9999 A.D. IsDate checks for date formats yyyy-m-d and yyyy/m/d. If neither matches, it tries converting the value to a number; if that succeeds, it tries assigning it as a date and checking for a year between 1 and 9999. If no match, it returns False. To evaluate to True: • There must be a field separator. • The expression must include a year (when converted to a date) between 1 and 9999, inclusive. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
Formats	This list shows expressions for January 1, 2003 that evaluate to True (-1) or False (0). Evaluates to True • IsDate("2003/01/01") • IsDate("01/01/2003") • IsDate("2003-01-01") • IsDate("03-01-01") • IsDate("01-01-2003") • IsDate("01-01-03") • IsDate("2003-1-1") • IsDate("January 01, 2003") • IsDate("01-Jan-03") • IsDate("2003.01.01") • IsDate("01.01.2003") Evaluates to False • IsDate("010103") • IsDate("January 01 2003") • IsDate("Jan 01 2003") • IsDate("Jan-01-2003") • IsDate("20030101") • IsDate("030101")
See Also	IsNull Function and IsNumeric Function
Example	Examine Field1 in the source file. If Field1 contains a valid date, then write "This is a valid date." to the target. If Field1 does not contain a valid date, then write "This is an invalid date." to the target. IIf(IsDate(FieldAt("/SOURCE/R1/Field1")) == -1, "This is a valid date.", "This is an invalid date.")

IsDirectory Function

Description	Determines whether a filename is a directory or not.
Return	• -1 (True) if the specified filename parameter is a directory • 0 (False) if the specified filename parameter is a file • ERROR if the filename parameter does not exist (is not a file or directory)
Return (Null)	Error: Incorrect argument count for function.
Syntax	IsDirectory(directoryname)
Parameters	This function has the following parameters: • directoryname - A string containing the name of the directory to be found. A complete path specification (either absolute or relative) must be provided if the directory is not expected to exist in the current working directory. If a string literal, enclose in quotation marks. Note: Use MacroExpand() while using macros to pass the parameters.
Remarks	The IsFile and IsDirectory functions check for three possible conditions (True, False, Error). Use the FileExists function first if you want to check for a file or directory without the possibility of returning an error. If it exists, you can then test with IsFile or IsDirectory. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	FileList Function, FileRead Function, and IsFile Function
Example	The following script tests for the existence of a directory. If FileExists("C:\temp") Then If IsDirectory("C:\temp") Then LogMessage("INFO","Is a Directory")) End If Else LogMessage("INFO","Not a Directory") End If

IsFile Function

This function works only with files. You cannot name a database file and specify a table. You cannot use this function with SQL-based connectors such as Oracle and SQL Server.

Description	Determines whether a file name is a file or not.
Return	• -1 (True) if the specified filename parameter is a file • 0 (False) if the specified filename parameter is a directory • ERROR if the filename parameter does not exist (is not a file or directory)
Return (Null)	Error: Incorrect argument count for function
Syntax	IsFile(filename)
Parameters	This function has the following parameters: • filename - A string containing the name of the file whose existence is to be determined. A complete path specification (either absolute or relative) must be provided if the file is not expected to exist in the current directory. If a string literal, enclose in quotation marks. Note: Use MacroExpand() while using macros to pass the parameters.
Remarks	The IsFile and IsDirectory functions check for three possible conditions (True, False, Error). Use the FileExists function first if you want to check for a file or directory without the possibility of returning an error. If it exists, you can then test with IsFile or IsDirectory. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	FileExists Function, FileList Function, FileRead Function, IsDirectory Function
Example	The following script tests for the existence of a file: If FileExists("C:\test.dbf") Then If IsFile("C:\test.dbf") Then LogMessage("INFO","Is a File")) End If Else LogMessage("INFO","Not a File") End If

IsMacroDefined Function

Description	Test and see if a macro is defined or not
Return	Boolean value indicating whether or not a macro is defined True returns a value of -1, while False returns a value of 0.
Return (Null)	Error: Incorrect argument count for function.
Syntax	IsMacroDefined(macroName)
Parameters	This function has the following parameters: • macroName - Name of the macro to determine whether it is defined or undefined. If a string literal, enclose in quotation marks.
Remarks	Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	DefineMacro Function, MacroValue Function, MacroExpand Function, ClearMacro Function
Example	Checks for the existence of a macro called "mymacro": IsMacroDefined("mymacro")

IsNull Function

Description	Test a value for a special null value
Return	Value that indicates whether or not another value contains the special null value. True returns a value of -1, while False returns a value of 0.
Return (Null)	True
Syntax	IsNull(value)
Parameters	This function has the following parameters: • value - Any expression. If a string literal, enclose in quotation marks.
Remarks	The IsNull function returns True if the expression contains the null value; otherwise, it returns False. The null value indicates that the value contains no data. Null is not the same as Empty, which indicates that a value has not yet been initialized. It is also not the same as a zero-length string, which is often referred to as a null string. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	IsDate Function and IsNumeric Function
Examples	The following expression examines source "Field1" and determines whether or not it is null: IsNull(FieldAt("/SOURCE/R1/Field1")) The following example builds upon the example above by using additional functions. It uses the IsNull function to determine whether the value of a field is null. If the field is null, a string stating that no data was provided is returned to the target. If the field is not null, the contents of source "Field1" are returned to the target: If IsNull(FieldAt("/SOURCE/R1/Field1")) Then "No data provided." Else FieldAt("/SOURCE/R1/Field1") End If

IsNumeric Function

Description	Test a value variable and determine if it can be converted to a numeric data type
Return	Boolean value indicating whether or not a value variable can be converted to a numeric data type. True returns a value of –1, while False returns a value of 0.
Return (Null)	Error: Incorrect argument count for function.
Syntax	IsNumeric(value)
Parameters	This function has the following parameters: • value - Any expression. If a string literal, enclose in question marks.
Remarks	This function returns True (-1) if value can legally be converted to a number; otherwise, it returns False (0). Some operating systems (including Windows) recognize the letters 'd' and 'D' as valid exponent notations. For example, the string "1d4" can be converted to a valid number (1x10^4, or 10,000). A valid number is composed of: • an optional sign (+ or -) • one or more decimal digits • an optional exponent consisting of: the letter 'd', 'D', 'e', 'E'. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	IsDate Function and IsNull Function
Example	The following expression examines the data in the source ("Account No") field and determines whether or not it can be converted to a numeric data type: IsNumeric(FieldAt("/SOURCE/R1/Account No"))

Join Function

Description	Concatenates the elements of an array together into a single string
Return	String consisting of all of the elements of an array
Return (Null)	Error: Too few arguments for function
Syntax	Join(sourceArray[,delimiter])
Parameters	This function has the following parameters: • sourceArray (required) - One-dimensional array containing substrings to be joined. • delimiter (optional) - String character used to separate the substrings in the returned string. If a string literal, enclose in quotation marks.
Remarks	If delimiter is omitted, the space character (" ") is used. If delimiter is a zero-length string (""), all items in the list are concatenated with no delimiters. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Split Function
Example	In the following examples, MyArray holds the names "John Smith", "Joe Jones", and "Dave Davis": Join(MyArray) Returns: John Smith Joe Jones Dave Davis In the second example, commas are added to separate the names: Join(MyArray,",") Returns: John Smith,Joe Jones,Dave Davis

LBound Function

Description	Find the index of the first element (lower bound) in an array or an array dimension
Return	Lower bound of an array
Return (Null)	Error: Invalid expression syntax.
Syntax	LBound(arrayName[,dimension])
Parameters	This function has the following parameters: • arrayName (required) - The array variable name. • dimension (optional) - Is a whole number indicating which dimension's lower bound is returned. Use 1 for the first dimension, 2 for the second, and 3 for the third. If dimension is omitted, 1 is assumed.
Remarks	Each element of an array stores one value and is referenced by using its index (coordinate position) after the array name. The index of the first element of an array is called its lower bound. The index of the last element is called its upper bound. (See UBound Function. ) By default, an array is indexed beginning with zero. The upper and lower bounds of an array can be inspected using LBound and UBound. Scalar and Undeclared Variables: Using scalar variables and/or undeclared variables results in a runtime error. Always declare all variables before using LBound. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
Example	Dim MyArray(1 To 100, 0 To 10, -5 To 5) LBound(MyArray) 'returns 1 LBound(MyArray, 1) 'returns 1 LBound(MyArray, 2) 'returns 0 LBound(MyArray, 3) 'returns -5

LCase Function

Description	Converts a string argument to lowercase.
Return	String in which all letters of an argument have been converted to lowercase
Return (Null)	Error: Incorrect argument count
Syntax	LCase(strexpr)
Parameters	This function has the following parameters: • strexpr - Any string expression or source field. If a string literal, enclose in quotation marks.
Remarks	LCase returns a string Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	InitCaps Function and UCase Function
Examples	This expression causes data in source "Field1" the to be returned to the target as all lowercase: LCase(FieldAt("/SOURCE/R1/Field1")) This example cleans the source "Field1" by making a standard case requirement in specific parts of addresses, such as "St" and "Ste". Any periods used in abbreviation are removed when data is returned to the target. Note the use of the Add1Part and InitCaps functions in the expression: Add1Part("NU", FieldAt("/SOURCE/R1/Field1"))&" "& Add1Part("PRD", FieldAt("/SOURCE/R1/Field1")) &" "& InitCaps(LCase(Add1Part("SN", FieldAt("/SOURCE/R1/Field1")))) & " "& InitCaps(LCase(Add1Part("SU", FieldAt("/SOURCE/R1/Field1")))) &" "& Add1Part("POD", FieldAt("/SOURCE/R1/Field1")) &" "& InitCaps(LCase(Add1Part("SAU", FieldAt("/SOURCE/R1/Field1")))) &" "& Add1Part("SAR", FieldAt("/SOURCE/R1/Field1")) &" "& Add1Part("REM", FieldAt("/SOURCE/R1/Field1"))

Left Function

Description	Returns the left part of a string.
Return	Leftmost n characters of a string argument
Return (Null)	Error: Incorrect argument count
Syntax	Left(string, length)
Parameters	This function has the following parameters: • string - Any string expression. If a string literal, enclose in quotation marks. • length - A Long expression indicating how many characters to return. It must be between 0 and approximately 65,500, inclusive. If length is 0, the return value is a zero-length string. If length is greater than or equal to the number of characters in string, the entire string is returned.
Remarks	Left returns a String. To find the number of characters in string, use Len(string). Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Len Function, Mid Function, and Right Function
Example	This example returns the three leftmost characters of the source field named ("Field1"): Left(FieldAt("/SOURCE/R1/Field1"), 3) OR Left(FieldAt("/SOURCE/R1/Field1"), 3) If field1 contains "Richard" Returns: Ric This example returns the five leftmost characters of the source field named ("Field1"): Left("Dr. Jane Doe II", 5) OR Left$("Dr. Jane Doe II", 5) Returns: Dr. J

LeftB Function

Description	Reads and returns the leftmost bytes of a string
Return	Leftmost bytes of a string
Return (Null)	Error: Incorrect argument count
Syntax	LeftB(string, length)
Parameters	This function has the following parameters: • string (required) - Any string. If a string literal, enclose in quotation marks. • length (required) - The length of bytes to return. It must be between 0 and 255, inclusive. If length is 0, the return value is a zero-length string. If length is greater than or equal to the bytes in string, the entire string is returned.
Remarks	LeftB returns a String. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Left Function, Right Function, RightB Function
Example	This example returns the three leftmost bytes of the source field "Address1": LeftB(FieldAt("/SOURCE/R1/Address",) 3) Address field contains "123 Main Street, New York, NY 10030" Returns: 123

Len Function

Description	Determines the length of a string in characters.
Return	Number of characters in a string expression
Return (Null)	Error: Incorrect argument count
Syntax	Len(strexpr)
Parameters	This function has the following parameters: • strexpr - Any string expression. If a string literal, enclose in quotation marks.
Remarks	Len returns the number of characters in the argument strexpr. Because Len works with user-defined data types, it is particularly useful for determining the record length when you are performing file input/output with random-access files. However, if a user-defined data type contains variable-length string elements, Len may not be able to properly determine the actual number of storage bytes required. If the argument to Len is null, the function returns a null. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	InputBox Function
Example	The following example returns the number of characters in a string expression: Len(FieldAt("/SOURCE/R1/Field1")) If Field1 contains value "Richard", then it returns 7. If Field1 contains value "Dr. James", then it returns 9.

LenB Function

Description	Determines the number of bytes required to store a variable
Return	Number of bytes required to store a variable
Return (Null)	Error: Incorrect argument count
Syntax	LenB(string)
Parameters	This function has the following parameters: • string - A string expression. If a string literal, enclose in quotation marks.
Remarks	Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Len Function
Example	Example 1 This example shows how LenB can be used to return the number of bytes needed to store the data in Field1: LenB(FieldAt("/SOURCE/R1/Field1")) If Field1 contains string "Richard" Return: 7 Example 2 LenB("Rose") Return: 4

Log10 Function

Description	Finds the base 10 logarithm of a number
Return	Base 10 logarithm of a number
Return (Null)	Error: Incorrect argument count
Syntax	Log10(number)
Parameters	This function has the following parameters: • number - Any valid numeric expression greater than zero.
Remarks	Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Log Function and Exp Function
Example	This example returns the base 10 logarithm of numbers in the source "Field1" to the target: Log10(FieldAt("/SOURCE/R1/Field1")) Applied to a field containing: 2147483647 Returns: 9

Log Function

Description	Determine the natural logarithm of a number
Return	Natural logarithm of a number.
Return (Null)	Incorrect argument count for function
Syntax	Log(number)
Parameters	This function has the following parameters: • number - Any valid numeric expression greater than zero.
Remarks	The natural logarithm is the logarithm to the base e. The constant e is approximately 2.718282. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Log10 Function and Exp Function
Example	This example returns the natural logarithm of numbers in the source "Field1" to the target: Log(FieldAt("/SOURCE/R1/Field1"))

LogMessage Function

Description	Creates an entry in the error and event log file while a transformation or batch is running
Return	Not applicable for this function
Return (Null)	Error: Incorrect argument count
Syntax	LogMessage(ErrString, MsgString)
Parameters	This function has the following parameters: • ErrString (required) - "ERROR", "WARN", "INFO", and "DEBUG" keyword labels for the various LogMessage types. An integer value representing the log level appears in the log file. Enclose in quotation marks if it is a string literal. • MsgString (required) - Text of the message that appears in the log. Enclose in quotation marks if it is a string literal.
Remarks	The maximum length of a message that can be returned by LogMessage is 2048 bytes. You must select the types of messages you would like to write to the transaction log file. Log messages generated by the LogMessage function are displayed only if the corresponding error type is selected.
Example	If FieldAt("/SOURCE/R1/Account No") > 10019 Then LogMessage("ERROR", "Account" & FieldAt("/SOURCE/R1/Account No") & " number exceeds 10019") End If The above expression produces messages like the following in the error and event log: 09/03/2003 12:31:45 Account 10030 number exceeds 10019 09/03/2003 12:31:45 Account 10031 number exceeds 10019 09/03/2003 12:31:45 Account 10032 number exceeds 10019

Lookup Function

Description	Lookup a value in an external table.
Return	Values from a two-column file.
Return (Null)	Error: Too few arguments for function.
Syntax	Lookup("value", "file", "sep" [, direction] [, default] [, encoding])
Parameters	This function has the following parameters: • key - The key to search for. • table - The name of the file containing the lookup table. • separator - The character separating the two columns of data; such as "," (comma) or "\|" (pipe). It must be a unique character that does not exist in the data. • keyColumn- Specifies whether the key column is the left column (0), or the right column (1). The Default is 0. • default - Optional. If no default is given, the expression returns an empty string if the input value or field is not found in the lookup file. If a default is given, the string specified as the default is returned if the input value or field is not found on the lookup file. Note: If you want to specify a default, you must also specify the direction. This is because Lookup uses the first optional argument it finds as the direction. • encoding - Optional. Enables you to specify the character encoding to be used when reading the lookup file. For example, ENC_CP437. For a list of all encodings, see Encoding Reference Note: If you want to specify encoding, you must also set the direction and a default. This is because Lookup uses the first optional argument it finds as the direction and the second as the default.
Remarks	Tip... The following section gives details on manually creating lookup files that can be accessed using the Lookup function. To manually create lookup files: Create and save a lookup file in a simple Text Editor. The file must contain at least two columns of data. Each line in the lookup file must contain a lookup key in one column, a separator (such as a comma or a pipe), and a corresponding value in the second column. Limitations of lookup files: Maximum line length is 4096 characters; maximum key length is 31 bytes (the field can be longer, but only the first 31 characters are used for indexing). Because of key length limitation, only first 31 characters are compared during search, irrespective of field length. Time of execution will increase with the size of the lookup file. Also, a smaller key is significantly faster. If you use the example syntax, Map Designer looks up the value in the lookup file and writes the corresponding value for that one key in every record in that field in the target data file. The LookupIndexDir file setting is used as the directory to store lookup function index files. If not set, the default is still the InstallDir/lookup directory. Modify this file setting in the UserInfo section of the cosmos.ini file. If the index directory cannot be created, or is not accessible as read/write, and the size of the lookup file is more than 32000 bytes then an error occurs. If the size is less than 32000 the lookup will continue to process without an index. Note: The use of duplicate keys is not permitted because they can result in index corruption that causes the lookup to behave incorrectly. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow
See Also	ResetLookup Function, GSub Function, Sub Function, StrReplace Function, TLookup Function, Transliterate Function, Xlate Function
Example	In this example, Map Designer looks for any key in the file lookup.txt, and looks for any found key in FIELD1 of the source file. Then Map Designer writes the corresponding values in the current field for every record in the target file. The separator between the value columns in the lookup file is a pipe ( \| ): Lookup(Trim(Fields("FIELD1")), "lookup.txt","\|") Trim is added to this expression to eliminate the possibility of extra spaces causing the lookup to fail. Use of Trim is recommended.

Lookup Function Instructions

This topic gives details on manually creating lookup files. However, you can use wizards to automatically create lookup files and their corresponding functions. For step-by-step instructions, search for “dynamic SQL lookups” in the documentation.

Manually Creating Lookup Files

The Lookup function resembles the GSub and Sub functions. When you use the Lookup function, you (1) search for specific data in a source file field and (2) replace the source file field data with values from an external target file.

You must first create a lookup file. This file consists of two columns of data. Each line in the file must contain a lookup key, a separator such as a comma or a pipe, and a corresponding value.

Suggestion: Create your lookup file in a Text Editor. Save the file in the default installation directory under the LookupTables subdirectory. As in the following example, you can also create a test directory called MyData:

C:\MyData\LOOKUP.TXT:

Mon|Monday

Tue|Tuesday

Wed|Wednesday

Thu|Thursday

Fri|Friday

Sat|Saturday

Sun|Sunday

The separator between the two columns is a pipe (|). Press Enter after typing each line.

There are four ways to do a lookup. The syntax for each is as follows:

Lookup("string", "file", "separator")

where

• "string" is a value from the first column in the file (key).

• "file" is the name of the external file.

• "separator" is the separator between the columns of data in the lookup file.

Map Designer writes the corresponding data from the second column in every record in your target data file.

Target Field Name	Target Field Expression
FIELD1	=Lookup(Fields("FIEL1D"), "lookup.txt", "\|")

The above example instructs Map Designer to write "Monday" in FIELD1 in every record in your target data file.

Lookup(Fields("FIELD1"), "file", "separator")

Where ("FIELD1") is the field name in the source data file in which Map Designer looks up values. Where "file" is the name of the external file. Where "separator" is the character that separates the columns of data in the lookup file. When values from the key column are found, Map Designer will write the corresponding value from the right column of the lookup file into the target data file.

Target Field Name	Target Field Expression
FIELD1	=Lookup(Fields("FIEL1D"), "lookup.txt", "\|")

This example instructs Map Designer to write "Monday" in FIELD1 of every record in your target data file where it finds "Mon" in FIELD1 of the source data file, and "Tuesday" in FIELD1 of every record in your target data file where it finds "Tue" in FIELD1 of the source data file, and so on.

Lookup("string", "file", "separator" [, reverse])

Lookup(Fields("FIELD1"), "file", "separator" [, reverse])

These work basically the same, but 'reverse' can instruct Map Designer to either look for the value in the first or second column in your external file, and replace it with the corresponding data. If 'reverse' is 0 (zero), Map Designer looks for the value in the first column and replace it with the value in the second column. If 'reverse' is not 0 (zero), Map Designer looks for the value in the second column and replaces it with the value in the first column (reverse order). This syntax gives you the opportunity to use the same external lookup file in multiple transformations.

Lookup File Notes

The following notes refer to lookup functions, but not to incore lookup functions.

• Max Key Len = 31 characters (the Key field (first column) can be longer than 31 chars, but Map Designer will just use the first 31 for key/indexing).

• Must have only 2 columns (left and right).

• No limit on number of rows (except performance - expect linear performance degradation; that is, bigger is slower). A lot depends on machine size/speed, and KeyLen (that is, a smaller key is faster).

• After making changes to a lookup file, call ResetLookup to enable lookup functions to use new values.

IMPORTANT! All lookup functions in an integration should use the exact same path, including capitalization and direction of slashes. Best practice is to use a macro in all cases so that the path is entered only once and therefore always identical.

Description	Trims leading spaces from a string.
Return	Specified string with leading (leftmost) spaces removed
Return (Null)	Error: Incorrect argument count
Syntax	LTrim(stringexpr)
Parameters	This function has the following parameters: • stringexpr - Any expression containing numeric or text characters. If a string literal, enclose in quotation marks.
Remarks	LTrim removes leading spaces from a string. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	RTrim Function and Trim Function
Example	The following example removes the leading spaces from the string " TestString": LTrim(" TestString") Result: "TestString"

Description	Expands macros embedded in a character string
Return	Expanded value of the specified macro
Return (Null)	Incorrect argument count for function
Syntax	MacroExpand(string)
Parameters	This function has the following parameters: • string - Any expression containing characters. If a string literal, enclose in quotation marks.
Remarks	This function takes a character string and returns a string with all macro occurrences expanded. MacroExpand returns an empty string for the value of an encrypted macro. Encrypted macros are only resolved by the integration engine at run time. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	DefineMacro Function, MacroValue Function, IsMacroDefined Function, ClearMacro Function
Example	For the following example, a macro named fileLocation contains the name of a folder (C:\MyFolder). Dim myString,fullString myString = "The file is located at $(fileLocation)." fullString = MacroExpand(myString) The value of fullString is set to: The file is located at C:\MyFolder.

Description	Looks up the value of a macro
Return	Defined value of a specified macro
Return (Null)	Error: Too few arguments for function
Syntax	MacroValue(macroName [,default])
Parameters	This function has the following parameters: • macroName (required) - String containing the name of the macro. If not defined, then default parameter is returned. If a string literal, enclose in quotation marks. • default (optional) - Default name of macro. Returned if no macroName parameter is specified (must be provided to be returned). If no default is provided, an empty string is returned.
Remarks	MacroValue returns an empty string for the value of an encrypted macro. Encrypted macros are only resolved by the integration engine at run time. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	DefineMacro Function, IsMacroDefined Function, MacroExpand Function, ClearMacro Function
Example	In this example, the defined macro called "mymacro" represents the path to the source file. It returns the following path: \myprocess\mysources\sourcefilename.asc: MacroValue("mymacro")

Description	Creates a new directory
Return	Not applicable for this function
Return (Null)	Error: Incorrect argument count
Syntax	MakeDirectory(dirName)
Parameters	This function has the following parameters: • dirName - A string containing the name of the directory to create. If a string literal, enclose in quotation marks. Note: Use MacroExpand() while using macros to pass the parameters.
Remarks	If dirName contains a path, all directories up to the parent of the new child directory must be included in the path and must exist. An error is returned if a directory cannot be created. This is usually because user permissions do not allow changes, a parent directory does not exist, or the directory already exists.
See Also	GetDirectory Function and RemoveDirectory Function
Example	This expression creates a directory named xmlfiles: MakeDirectory("C:\xmlfiles")

Description	Returns a string that is part of some other string.
Return	Specified portion of a string
Return (Null)	Error: Too few arguments for function
Syntax	Mid(string, start, length)
Parameters	This function has the following parameters: • string (required) - String expression from which another string is created. This can be any string expression. If a string literal, enclose in quotation marks. • start (required) - Long expression that indicates the character position in string at which the part to be taken begins. • length (optional) - Long expression that indicates the number of characters to return.
Remarks	The parameters start and length must be between 1 and approximately 65,500, inclusive. If length is omitted or if there are fewer than length characters in the text (including the character at start), the Mid function returns all characters from the start position to the end of the string. If start is greater than the number of characters in string, then Mid returns a zero-length string. Use the Len function to find the number of characters in string. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Left Function, Len Function, and Right Function
Example	Example 1: The following function returns a part of the string: Mid("Hello", 3, 2) Returned Value: ll Example 2: If the field contains value 20201207 date format string. This functions converts the date into a 12/07/2020 date string: Mid(FieldAt("/SOURCE/R1/Field1"), 5, 2) Returned Value: 12 Mid(FieldAt("/SOURCE/R1/Field1"), 7, 2) Returned Value: 07 Mid(FieldAt("/SOURCE/R1/Field1"), 1, 4) Returned Value: 2020 The preceding expressions are appended with a "/" in between the returned value. Mid(FieldAt("/SOURCE/R1/Field1"), 5, 2) & "/" & Mid(FieldAt("/SOURCE/R1/Field1"), 7, 2) & "/" & Mid(FieldAt("/SOURCE/R1/Field1"), 1, 4) Returned value: 12/07/2020

Description	Returns a specific number of characters from a text string, starting at the position you specify, based on the number of bytes you specify.
Return	Returns part of a string, specified in bytes.
Return (Null)	Error: Too few arguments for function
Syntax	MidB(string, start, length)
Parameters	This function has the following parameters: • string- A string expression. • start - The starting byte position of the substring to return. • length - The number of bytes to return.
Remarks	• If start is greater than the number of characters in string, then MidB returns a zero-length string. • If start is less than the length of text, but length exceeds the length of text, then MidB returns the characters up to the end of text.
See Also	Mid Function, LeftB Function, RightB Function, Right Function, Left Function, Len Function, LenB Function
Example	The following examples returns a part of the string: Example 1: MidB("Hello", 3, 2) It returns two characters from the third position of the string. Returned Value: ll Example 2: MidB("$3,000.00",2,1) It returns one character from the second position of the string. Returned Value: 3

Description	Convert clock time to a minute integer
Return	Integer between 0 and 59, inclusive, which represents the minute of the hour corresponding to the time provided as an argument
Return (Null)	Error: Incorrect argument count
Syntax	Minute(dateString) - if containing time value. Or Minute(TimeString)
Parameters	This function has the following parameters: • dateString - If containing time value. Or • TimeString - Any value or expression that can represent a date/time.
Remarks	Numbers to the left of the decimal point in number represent the date; numbers to the right represent time. Negative numbers represent dates prior to December 30, 1899. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	Day Function, Hour Function, Now Function, Second Function, Weekday Function, Year Function
Example	The example below returns the minute portion of the current system time (if system time is 1:12:30, "12" is returned to the target): Minute(Now()) The example below returns the minute portion from the source ("Minute"). The TimeValue function defines each time to be time values, while the Minute function defines those values as minutes: a = TimeValue(Trim(FieldAt("/SOURCE/R1/Minute"))) Minute(a)

Description	Find the month integer of a date
Return	Integer between 1 and 12, inclusive, which represents the month of the year for a date argument
Return (Null)	Error: Too few arguments for function
Syntax	Month(dateString)
Parameters	This function has the following parameters: • dateString - Any value or expression that can represent a date.
Remarks	Numbers to the left of the decimal point in number represent the date; numbers to the right represent the time. Negative numbers represent dates prior to December 30, 1899. If number is null, this function returns a null. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	DatePart Function, Day Function, Hour Function, Minute Function, Now Function, Second Function, Weekday Function, Year Function
Example	This example will examine the data in the source field called ("ShipDate"), which contains a valid date, and return the two-digit month designation to the target field: Month(FieldAt("/SOURCE/R1/ShipDate")) The example below returns the month portion from dates in the source ("ShipDate"). The DateValue function defines each date as date values, while the Month function defines those values as months: a = DateValue(Trim(FieldAt("/SOURCE/R1/ShipDate"))) Month(a)

Description	Displays a message dialog and waits for the user to choose a button.
Return	Value indicating which button the user has chosen.
Return (Null)	Error: Incorrect argument count
Syntax	MsgBox(Msg, [Style], [Title])
Parameters	This function has the following parameters: • msg - String expression displayed as a message dialog. If a string literal, enclose in quotation marks. MsgBox(“msg”). • style - (Optional) Style is a numeric expression that is the sum of values indicating the number and style of buttons to display, the style of icon to use, the identity of the default button, and the modality. The following table lists the values you may use: – 0: Display OK button only. – 1: Display OK and Cancel buttons. – 2: Display Stop, Retry, and Ignore buttons. – 3: Display Yes, No, and Cancel buttons. – 4: Display Yes and No buttons.input – 5: Display Retry and Cancel buttons. – 16: Display a stop sign icon. – 32: Display a question mark icon. – 48: Display an exclamation icon. – 64: Display an information icon. – 0: First button is default. – 256: Second button is default. – 512: Third button is default. – 0: Application modal. The user must respond to the message box before continuing work in the current application. – 4096: System modal. All applications are suspended until the user responds to the message box.
	The first group of values (1 - 5) describes the number and type of buttons displayed in the dialog. The second group (16, 32, 48, 64) describes the icon style. The third group (0, 256, 512) determines the default button. The fourth group (0, 4096) determines the modality of the message box. When adding numbers to create a final value for the argument type, use only one number from each group. If type is excluded, the default value for type is 0 (zero).
title	The string expression displayed in the title bar of the dialog. If you exclude the title, the title bar displays RIFL Script MsgBox as the default title. For application modal message boxes, MsgBox displays a maximum of 1024 characters. Longer messages are truncated. Message strings longer than 255 characters with no intervening spaces are truncated after the 255th character. For system modal message boxes, the number of characters that can be displayed depends on the screen resolution and whether or not the string to be displayed is one or more lines. MsgBox breaks lines automatically at the right edge of the dialog. If the user wants to set their own line breaks, a linefeed (ANSI 10) must be placed before the first character of the text that begins on each new line. The value returned by the MsgBox function indicates the selected button: – 1: OK button was selected. – 2: Cancel button was selected. – 3: Stop button was selected. – 4: Retry button was selected. – 5: Ignore button was selected. – 6: Yes button was selected. – 7: No button was selected. If the dialog displays a Cancel button, pressing Esc has the same effect as selecting Cancel.
Remarks	This function has three parameters, msg, type and title. Caution! This function returns a value that must be used or stored to prevent stack overflow. See Stack Overflow.
See Also	InputBox Function
Example 1	‘Causes a message box with a Stop sign to pop up for every record that is being converted. The message box displays "This is the Account Number", and the actual account number as it is stored in the Account Number field in the source data file: MsgBox("This is the Account Number: " & Fields("Account No"),16, "Account Number")
Example 2	Display a YES/NO message box asking the user whether to continue: If MsgBox("Do you want to continue?", 4, "Validation Error") = 7 Then The user clicks the No button; stop the transformation: Stop(-1) Else The user clicks the Yes button; discard the record and continue the transformation: Discard(-1) End If