Functions
Functions

Functions

image

A function takes zero or more parameters, performs some calculation, and then returns a single value.

A wide range of functions are available to help you perform different tasks – these are listed in the table below.

In addition, two specific functions, Data(name) and System(name) are also available, which return source table data and system metadata respectively — these are described [page here]

Eightwire is generally case-insensitive. This means that you can write your expressions in uppercase, lowercase, or any combination, provided you spell things correctly.

When providing function parameters for comparison/searching type activities, these will work no matter what their case, provided they are otherwise an exact match – pay attention to any spaces or other punctuation, these count when searching or comparing things.

image

String Manipulation

PropertyExpressionDescription
String
Find(value, before, after)
Return the text in value between before and after. For example, the following expression returns " quick ": Find("The quick brown fox","The ", "Brown")
String
First(value, length)
Return the first length characters from value. For example, the following expression returns "The qui": First("The quick brown fox", 7)
String
Last(value, length)
Returns the last length characters from value. For example, the following expression returns "own fox": Last("The quick brown fox", 7)
String
FirstFilled(value [,.., value])
Returns the first value parameter that is not empty, not null and not encoded-null (chr(0)). This function takes one or more parameters. The left-most parameter is checked first, then the next, and so on, until one is found that is not blank and this value is returned. If no non-blank parameters are found, an empty string is returned (""). For example, the following expression returns "fox": FirstFilled("", "", "", "Fox", "")
String
Lowercase(value)
Returns value as lowercase text. For example, the following expression returns "brown fox": Lowercase("Brown Fox")
String
IndexOf(value, find)
Returns the left-most character index of find within value. If find does not occur as a substring of value then -1 is returned. Character indexes are 0-based. For example, the following expression returns 1: IndexOf("Fog Dog Log", "og")
String
LastIndexOf(value, find)
Returns the right-most character index of find within value. If find does not occur as a substring of value then -1 is returned. Character indexes are 0-based. For example, the following expression returns 9: LastIndexOf("Fog Dog Log", "og")
String
PadLeft(value, length) PadLeft(value, length, padding)
Returns value, right-aligned, padded with the padding character, resulting in text length characters long. If padding is not provided, the result will be padded with whitespace. For example, the following expression returns "--Fox": PadLeft("Fox", 5, "-")
String
PadRight(value, length) PadRight(value, length, padding)
Returns value, left-aligned, padded with the padding character, resulting in text length characters long. If padding is not provided, the result will be padded with whitespace. For example, the following expression returns "Fox--": PadRight("Fox", 5, "-")
String
Replace(value, find, replace)
Finds all occurrences of find in value and replaces them with replace, returning the resulting text. For example, the following expression returns "The slow fox": Replace("The quick fox", "quick", "slow")
String
SubString(value, start) SubString(value, start, length)
Returns length characters in value, starting from character position start, which is 0-based. If length is not provided, the remainder of the text is returned after start. For example, the following expression returns "ui": SubString("The quick fox", 5, 2)
String
Token(value, index) Token(value, index, separator)
Splits value into multiple tokens, using the separator character (or whitespace if not provided), then returns the index token (0-based). For example, the following expression returns "brown": Token("The quick brown fox", 2)
String
FirstToken(value) FirstToken(value, separator)
Splits value into multiple tokens, using the separator character (or whitespace if not provided), then returns the first (left-most) token. For example, the following expression returns "The": FirstToken("The quick brown fox")
String
LastToken(value) LastToken(value, separator)
Splits value into multiple tokens, using the separator character (or whitespace if not provided), then returns the last (right-most) token. For example, the following expression returns "fox": LastToken("The quick brown fox")
String
Trim(value)
Returns value with any leading and trailing whitespace removed. For example, the following expression returns "fox": Trim(" fox ")
String
TrimLeft(value)
Returns value with any leading whitespace removed. For example, the following expression returns "fox ": TrimLeft(" fox ")
String
TrimRight(value)
Returns value with any trailing whitespace removed. For example, the following expression returns "fox": TrimRight(" fox ")
String
Uppercase(value)
Returns value as uppercase text. For example, the following expression returns "BROWN FOX": Uppercase("Brown Fox")

Number Manipulation

PropertyExpressionDescription
Number
Round(value) Round(value, decimal places)
Returns the number in value, rounded to decimal places. If decimal places is not provided, zero decimal places will be assumed. For example, the following expression returns 5.75: Round(5.7498, 2)
Number
RoundDown(value)
Returns the number in value, rounded down to the nearest integer. For example, the following expression returns 17: RoundDown(17.875)
Number
RoundUp(value)
Returns the number in value, rounded up to the nearest integer. For example, the following expression returns 11: RoundUp(10.0025)

Date & Time Manipulation

PropertyExpressionDescription
Date & Time
DateAdd(value, unit, interval)
Returns the date in value with interval number of units added. Interval can be positive or negative. Unit must be one of the following: Second, Minute, Hour, Day, Month, Year. For example, the following expression returns "1974-09-01 00:00:00.0000000": DateAdd("1 Jun 1974", "Month", 3)
Date & Time
DatePart(value, datepart)
Returns the datepart component of value. Datepart must be one of the following: Second, Minute, Hour, Day, DayOfWeek, DayOfYear, Month, MonthAbbreviation, MonthName, Year. For example, the following expression returns "1974": DatePart("1 Jun 1974", "Year")

Conversions

PropertyExpressionDescription
Conversions
ToBinary(value)
Returns value, converted to encoded binary. Value can be of any type, except binary. For example, the following expression returns "0x6F006B00": ToBinary("ok")
Conversions
ToBoolean(value)
Returns value, converted to Boolean (1 or 0). Value must be one of the following: 1, 0, t, f, true, false, y, n, yes, no For example, the following expression returns 1: ToBoolean("Yes")
Conversions
ToDate(value)
Returns value, formatted as a standard date, provided value contains a valid date in a format that is recognised. For example, the following expression returns "2004-11-27": ToDate("2004-11-27 10:45pm")
Conversions
ToDateTime(value)
Returns value, formatted as a standard date and time, provided value contains a valid date or datetime in a format that is recognised. For example, the following expression returns "2004-11-27 22:45:00.0000000": ToDateTime("2004-11-27 10:45pm")
Conversions
ToNumber(value)
Returns value formatted as a number, provided value contains a valid number. This can be used when a text literal contains a number but might be treated as text unless we explicitly tell the system it’s a number. This also supports conversion from scientific notation. For example, the following expression returns "602200": ToNumber("6.022e5")
Conversions
ToText(value)
Returns the text representation of value. If value is valid encoded binary, then value will be decoded and returned as text. If the encoded data is not text, this may result in unexpected characters and text if you try to convert it to text. For example, the following expression returns "ok": ToText("0x6F006B00") For any other data type, the text representation of its value will be returned. For Boolean types, this means returning either "True" or "False". If value is a number, calling ToText will force it to be treated as text instead. This can be used when you want to treat a number as if it were text and perform concatenation rather than arithmetic using the ‘+’ operator. For example, the following expression returns "43": ToText(4) + 3
Conversions
ToTime(value)
Returns value, formatted as a standard time, provided value contains a valid time in a format that is recognised. For example, the following expression returns "22:45:00": ToTime("10:45pm")
Conversions
ToXML(value) ToXML(value, tagname)
Converts value to XML. This method works in one of two ways: If value is valid JSON, it is converted to XML and returned. Only the value parameter should be provided. If value contains an encoded binary representation of XML, the decoded XML is returned. Only the value parameter should be provided. If value contains any other type of data, a tagname should be provided. A single-node XML document is returned. For example, the following expression returns "<fox>brown</fox>": ToXML("brown", "fox") To build more complex XML, consider using the ToTemplate function.
Conversions
ToJSON(value)
Converts the XML in value to JSON format. This method only converts from XML to JSON. If value is valid XML, it is converted to JSON and returned. If value contains an encoded binary representation of JSON, the decoded JSON is returned. No other data types for value are allowed.

Validation

PropertyExpressionDescription
Validation
IsBinary(value)
Returns true if value can be successfully converted to binary. In Eightwire, binary is encoded as hexadecimal text to make it easier to handle. Usually (but not always), binary data starts with the prefix "0x"; this is followed by a sequence of two-digit hexadecimal numbers. For example "0x29ABE2" or just "29ABE2". The "0x" prefix helps to positively identify this data as binary instead of some other type of text. For example, the following expression returns True: IsBinary("0x29ABE2")
Validation
IsBoolean(value)
Returns true if value is a recognised Boolean value. The recognised Boolean values are: 1, 0, true, false, t, f, yes, no, y, n For example, the following expression returns True: IsBoolean("NO")
Validation
IsDate(value)
Returns true if value contains a valid date or datetime value. Eightwire recognises a wide range of date formats. Some common examples are: 23/04/2017 23/04/17 04-23-2017 04-23-17 23rd April, 2017 23-Apr-17 23.APR.2017 20170423 23/05/17 5/23/2017 Internally Eightwire uses 23-APR-2017, and this is the format that will be returned from functions like ToDate. For example, the following expression returns True: IsDate("23rd April 2017")
Validation
IsDateTime(value)
Returns true if value contains a valid date or datetime value. See IsDate and IsTime for examples of valid formats. For example, the following expression returns True: IsDateTime("23/4/2017 1:30pm")
Validation
IsNumber(value)
Returns true if value is a valid number. Valid numbers include integers, decimals and scientific notation. Eightwire recognises and can manipulate any standard number up to 28 digits. Scientific notation and numbers up to 38 digits are recognised and can be moved directly using Eightwire but cannot be manipulated using expressions. Examples of scientific notation are: 6.02E+23 1.60E-35 For example, the following expression returns True: IsNumber("-234533.50")
Validation
IsText(value
Returns true if value is text or can be converted directly into text, including an empty string or a string containing only chr(0). Because every type can be represented as text, this function will always return true unless value is null. For example, the following expressions both return True: IsText("Hello World") IsText("") However, the following will return False: IsText(null)
Validation
IsTime(value)
Returns true if value contains a valid time or datetime value. Eightwire recognises a wide range of time formats. Some common examples are: 45:00.0 1:45pm 1:45:00 p.m. Internally Eightwire uses 13:45:00.000000000, and this is the format that will be returned from functions like ToTime and ToDateTime. For example, the following expression returns True: IsTime("5:30am")
Validation
IsXML(value)
Returns true if value appears to contain XML. This function does not do a full validation check and it much quicker to run than IsValidXML. This function may return true even if the XML is malformed. For example, the following expression returns True: IsXML("<fox>quick</fox>")
Validation
IsValidXML(value)
Returns true if value contains valid XML. This function performs a full validation check. This function may be slow to run compared with IsXML because fully validating XML can be quite time-consuming. For example, the following expression returns True: IsValidXML("<fox>quick<fox>") In the following examples, IsXML would return True because it does not thoroughly check the XML, but IsValidXML would fully validate the XML and find an unclosed tag, so it would return False: IsXML("<fox><quick></fox>") returns true. IsValidXML("<fox><quick></fox>") returns false.
Validation
IsValidJSON(value)
Returns true if value contains valid JSON. This function performs a full validation check.
Validation
Contains(value, find)
Returns true if find is a substring of value. For example, the following expression returns true: Contains("quick brown fox", "bro")
Validation
BeginsWith(value, find)
Returns true if find matches the left-most portion of value. For example, the following expression returns true: BeginsWith("quick brown fox", "qui")
Validation
EndsWith(value, find)
Returns true if find matches the right-most portion of value. For example, the following expression returns true: EndsWith("quick brown fox", "ox")
Validation
IsEmpty(value)
Returns true if value, when converted to test, contains no characters – an empty string. For example, the following expression returns true: IsEmpty("")
Validation
IsIn(find, item [,.., item])
Returns true if find (the first parameter) matches any of the subsequent item parameters. You can call this function with 2 or more parameters. For example, the following expression returns true: IsIn("fox", "dog", "fox", "wolf", "dingo")
Validation
IsNull(value)
Returns true if value is null, is text containing a single chr(0) or is text containing the escape sequence "\0" – all valid representations of null. An empty string will return false because it is not null. For example, the following expressions all return true: IsNull(null) IsNull("\0") IsNull(chr(0))
Validation
IsNullOrEmpty(value)
Returns true if value is null (as described in IsNull) or empty (as described in IsEmpty).
Validation
Hash(value)
Returns a 22-character hash of value. The hash function is used to map data of arbitrary size to data of fixed size. The text returned by the hash function is called a hash code. The hash function uses the MD5 algorithm. A hash code cannot be reversed to recover the original data, nor is a hash code guaranteed to be unique for a given input – however for the vast majority of real-world circumstances it will be unique. Please note: Hashing is not encryption.
Validation
ShortHash(value)
Returns a 5-character hash of value. See hash, above. A 5-character hash is less likely to be unique for a given input than a 22-character hash. However, if your input data is usually short then a shorter hash will likely suffice. Please note: Hashing is not encryption.

Formatting

PropertyExpressionDescription
Formatting
Chr(index)
Returns a single ASCII text character, represented by the character index. For example, the following expression returns "A": Chr(65) For a full list of ASCII character indexes, Google ‘ASCII’.
Formatting
Format(value, format)
Returns value formatted according to the format specified. The following data types can be formatted: Date DateTime Time Number Boolean The format parameter contains either a standard format or a custom format. The available formats are too numerous to list here, but they follow the Microsoft MSDN formatting rules. References for the various data types can be found here: Date and DateTime – Standard Formats Date and DateTime – Custom Formats Time – Standard Formats Time – Custom Formats Number – Standard Formats Number – Custom Formats Boolean: Formatting a Boolean value is simple – just provide either the positive or negative aspect you wish to use as a template. For example, the format "y" will result in value being formatted as either "y" or "n". The valid Boolean formats are: 1, 0, true, false, t, f, yes, no, y, n
Formatting
FromJSON(json, xpath)
This function is similar to the FromXML function below, but it queries a JSON document instead. Under the covers, Conductor converts your JSON document into XML and then uses XPath to query it. Please see FromXML, below, for more information on this.
Formatting
FromXML(xml, xpath)
This function executes an XPath query against the XML document and returns the value specified in the xpath query. XPath is a standard language for referring to specific information within XML documents. It can be used for many things, but here it must be used to locate either one tag value or attribute value within the XML document. If a value is found by the XPath query it is returned by this function. If the query fails to locate a value or is malformed, an error will occur, explaining the problem. Describing how to use XPath is beyond the scope of this document, but if you Google ‘xpath’ you should find information. Failing that, try this introduction by W3C
Formatting
ToTemplate(template)
Merges row and system data using a marked up template and returns the resulting formatted information. Template is any piece of text, but it may contain special tags which will be replaced with real data when the expression is executed. These special tags look like this: {data:PhoneNumber} {system:SourceDataStoreName} For the valid options for each, see the description for the Data and System functions below. For example, consider this template: ToTemplate("Name: {data:LastName}, {data:FirstName}. Age: {data:Age}") Assuming the row data contains all three columns mentioned in the template parameter, this function call might return something like this: "Name: Smith, Jane. Age: 47"

Miscellaneous

PropertyExpressionDescription
Miscellaneous
Length(value)
Converts value into text and returns the number of characters it contains, including whitespace. For example, the following expression returns 19: Length("The Quick Brown Fox")

Special

PropertyExpressionDescription
Special
Data(name)
Returns the row data in column name. For example, if the Process this expression is used in is reading data from a table that contains a column named "ID", then the following expression would return the value in that column for the current row - such as 23701: Data("ID") If there is no column name matching the name parameter, an empty string is returned.
Special
System(name)
Returns the named system data value specified. Aside from your own data that the Process is actually moving from place to place, you may wish to refer to some of the meta data that Conductor make available that describes the job. For example, the following expression returns the date and time that the Process was executed – such as "23 Jul 2017 14:30:00.000": System("BatchExecuteDate) Unlike the Data function, System data won’t change row-by-row and will remain constant throughout the execution of the Batch. See Appendix A for the full list of System Data available.
Special
RowIndex()
Returns the current row number, starting from 1. It is important to note that the order in which rows are read from the source data store is what dictates the row numbering here. This order is usually the same as how you would view this data through other means, but depending on the source data platform this may not always be the case. This number applies after any filtering has been done – filtering will not cause gaps in the numbering sequence. For example, if the following expression is run on the 3rd row of your table, it would return 3: RowIndex() This function does not require any parameters but you do need to include the empty parentheses as shown so that it is recognised as a function.