Regular Expression Functions

Regular Expression Functions

Click the following links to learn more about the functions supported in Arkieva.

Scalar Functions

Using scalar functions you can perform calculations on a single argument to return a new single value for a field. Cardinality = 1.

Add

Argument DataType Description
Item1 Numeric First item to add.
Item2 Numeric Second item to add.
Return If either item is Float, then the Type is Float. If either item is Decimal, then the Type is Decimal. If either item is Integer, then the Type is Integer.

Subtract

Argument DataType Description
Item1 Numeric Item from which to subtract.
Item2 Numeric Item to subtract.
Return If either item is Float, then the Type is Float. If either item is Decimal, then the Type is Decimal. If either item is an Integer, then the Type is Integer.

Multiply

Argument DataType Description
Item1 Numeric First item to multiply.
Item2 Numeric Second item to multiply.
Return If either item is Float, then the Type is Float. If either is Decimal, then the Type is Decimal. If either item is an Integer, then the Type is Integer.

Divide

Argument DataType Description
Item1 Numeric Item to divide into.
Item2 Numeric Item to divide by.
Return If either item is Float, then the Type is Float. If either item is Decimal, then the Type is Decimal.

Power

Argument DataType Description
Base Numeric Base to take to a power.
Exponent Numeric The exponent.
Return If either item is Float, then the Type is Float. If either item is Decimal, then the Type is Decimal. If either item is an Integer, then the Type is Integer.

Negate

Argument DataType Description
Item Numeric Item to negate.
Return Type is the same as the Item.

Mod

Argument DataType Description
Item1 Integer Item to divide into.
Item2 Integer Item to divide by.
Return Integer The remainder of the division.

Equals

Argument DataType Description
Item1 Boolean, DateTime, Integer, Decimal, Float, String, or EntityKey First item to compare.
Item2 Second item to compare (Item 1 and Item2 must have the same data type).
Return Boolean Indicates whether the items are the same.

NotEquals

Argument DataType Description
Item1 Boolean, DateTime, Integer, Decimal, Float, String, or EntityKey First item to compare.
Item2 Second item to compare (Item 1 and Item2 must have the same data type).
Return Boolean Indicates whether the items are not the same.

GreaterThan

Argument DataType Description
Item1 DateTime, Integer, Decimal, Float, or String First item to compare.
Item2 Second item to compare (Item 1 and Item2 must have the same data type).
Return Boolean Indicates whether the first item is greater than the second item.

GreaterThanOrEquals

Argument DataType Description
Item1 DateTime, Integer, Decimal, Float, or String First item to compare.
Item2 Second item to compare (Item 1 and Item2 must have the same data type).
Return Boolean Indicates whether the first item is greater than or equal to the second item.

LessThan

Argument DataType Description
Item1 DateTime, Integer, Decimal, Float, or String First item to compare.
Item2 Second item to compare (Item 1 and Item2 must have the same data type).
Return Boolean Indicates whether the first is greater than or equal to the second item.

LessThanOrEquals

Argument DataType Description
Item1 DateTime, Integer, Decimal, Float, or String First item to compare.
Item2 Second item to compare (Item 1 and Item2 must have the same data type).
Return Boolean Indicates whether the first item is less than or equal to the second item.

And

Argument DataType Description
Item1 Boolean First condition.
Item2 Boolean Second condition (If Item1 is false, Item2 is not evaluated).
Return Boolean If both Item1 and Item2 are true, the Return value is true.

Or

Argument DataType Description
Item1 Boolean First condition.
Item2 Boolean Second condition (If Item1 is true, Item2 is not evaluated).
Return Boolean If either Item1 or Item2 are true, the Return value is true.

Not

Argument DataType Description
Item Boolean Condition to negate.
Return Boolean If Item is false, the Return value is true.

Truncate

Argument DataType Description
Item Decimal or Float Item to truncate.
Digits Integer Number if decimal digits to which the item should be truncated. For example, specify three to truncate the item after the third digit to the right of the decimal.
Return The Return value type is the same as the item being truncated.

Round

Argument DataType Description
Item Decimal or Float Item to round.
Digits Integer Number if decimal digits the item should be rounded to; i.e., specify three to round the item to the third digit to the right of the decimal.
Return The Return value type is the same as the item being rounded.

Integer

Argument DataType Description
Item Numeric or String Item to cast (In Report Builder, Integer is listed on the Function tab as INT).
Return Integer The item cast as an integer. Of the item is a numeric value, it is truncated. Note that the invariant locale is used to case numeric strings. A period is the decimal separator. The comma used to separate thousands is not allowed.

Decimal

Argument DataType Description
Item Numeric or String Item to cast.
Return Decimal The item cast as a decimal.

Float

Argument DataType Description
Item Numeric or String Item to cast.
Return Float The item cast as a float.

String

Argument DataType Description
Item Numeric Item to cast (In Report Builder, String is listed on the Function tab as TEXT).
Return String Item to cast as a string.

Length

Argument DataType Description
String String String used to determine the length.
Return Integer Length of the string, specified as the number of characters within the string.

Find

Argument DataType Description
String String String that is searched for a contained string.
Substring String The substring to search for.
Return Integer Position of the first instance of the substring within the string (If the substring is not found, 0 is returned).

Substring

Argument DataType Description
String String String from which a substring is extracted.
Start Integer Start position within the string (1-based).
Length Integer Number of characters.
Return String The substring extracted from the string that contains the characters from Start to Start+Length.

Left

Argument DataType Description
String String String from which to obtain the leftmost characters.
Length Integer Number of characters.
Return String The substring of the string that contains the characters from 1 to Length.

Right

Argument DataType Description
String String String from which to obtain the rightmost characters.
Length Integer Number of characters.
Return String The substring of the string from Length(String)-Length+1 to Length(String).

Concat

Argument DataType Description
String1 String First string to concatenate.
String2 String Second string to conatenate.
Return String The second string concatenated to the end of the first string.

Lower

Argument DataType Description
String String String to convert to lowercase.
Return String String with all uppercase characters converted to lowercase.

Upper

Argument DataType Description
String String String to convert to upper case.
Return String String with all lowercase characters converted to uppercase.

LTrim

Argument DataType Description
String String String from which to trim leading spaces.
Return String String with all leading spaces removed.

RTrim

Argument DataType Description
String String String from which to trim trailing spaces.
Return String String with trailing spaces removed.

Replace

Argument DataType Description
String String String in which to replace all instances of one substring with another.
Find String The substring to search for.
Replace String The substring that replaces the Find string.
Return String String with all instances of Find replaced with Replace.

Date

Argument DataType Description
Year Integer Year for the date.
Month Integer Month (1-12) for the date.
Day Integer Day (1-31) for the date which must be a valid day of the specified month and year.
Return DateTime A datetime with the given year, month, and day at 00:00:00.

DateTime

Argument DataType Description
Year Integer Year for the date.
Month Integer Month (1-12) for the date.
Day Integer Dat (1-31) for the date which must be a valid day of the specified month and year.
Hour Integer Hour (0-23) for the time.
Minute Integer Minute (0-59) for the time.
Second Decimal Second (0-60) for the time.
Return DateTime A datetime with the specified year, month, day, hour, minute, and second.

Time

Argument DataType Description
DateTime DateTime Datetime from which to extract the time (Returns the hour, minute, and second from a datetime value).
Return Time Time from the datetime.

Year

Argument DataType Description
DateTime DateTime Date from which the year is extracted.
Return Integer Year of the datetime.

Quarter

Argument DataType Description
DateTime DateTime Date from which the quarter is extacted.
Return Integer Quarter (1-4) of the datetime.

Month

Argument DataType Description
DateTime DateTime Date from which the month is extracted.
Return Integer Month (1-12) of the datetime.

Day

Argument DataType Description
DateTime DateTime Date from which the day is extracted.
Return Integer Day (1-31) of the datetime.

Hour

Argument DataType Description
DateTime DateTime or Time Date or time from which the hour is extracted.
Return Integer Hour (0-23) of the datetime.

Minute

Argument DataType Description
DateTime DateTime or Time Date or time from which the minute is extracted.
Return Integer Minute (0-59) of the datetime.

Second

Argument DataType Description
DateTime DateTime or Time Date or time from which the second is extracted.
Return Integer Second (0-60) of the datetime.

DayofYear

Argument DataType Description
DateTime DateTime Date from which the day of the year is extracted.
Return Integer Day of year (1-366) of the datetime.

Week

Argument DataType Description
DateTime DateTime Date from which the week is extracted.
Return Integer Week (1-53) of the Datetime. The first day of the week is determined by the default first day of week associated with the culture of the semantic model.

DayofWeek

Argument DataType Description
DateTime DateTime Date from which the day of the week is extracted.
Return Integer Day of week (1-7) of the Datetime. Values start with Monday=1 through Sunday=7.

Date

Argument DataType Description
DateTime DateTime Date from which to remove the time (In Report Builder, this Date function is listed on the Function tab as DATEONLY).
Return Integer Datetime with the time cleared (00:00:00).

Now

Argument DataType Description
Return DateTime Current Datetime. Now is a static function.

Today

Argument DataType Description
Return DateTime Current datetime with the time cleared (00:00:00). Today is a static function.

DateDiff

Argument DataType Description
Interval String The units used to specify the date difference. Must be one of the following: Year, Quarter, Month, Day, Hour, Minute, Second, or Week. Must be a literal. If the formula calculates the difference in a start time and an end time, the interval unit may only be HOUR, MINUTE, or SECOND.
Start DateTime or Time The start date or time.
End DateTime or Time The end date or time. Must have the same DataType as the start time.
Return Integer The difference between the Start datetime and the End datetime, is the same units specified in the Interval. If the start datetime is after the end datetime, the result is negative.

DateAdd

Argument DataType Description
Interval String The units used to specify the date or time addition. Must be one of the following: Year, Quarter, Month, Day, Hour, Minute, Second, or Week. Must be a literal. If the formula adds a number of time interval units to a start time, the interval unit may only be HOUR, MINUTE, or SECOND.
Number Integer The number of units of Interval to add to the date or time.
DateTime DateTime or Time The date or time to add to.
Return DateTime The date or time that is the result of adding the specified number of Interval units to the original date or time. Must have the same data type as the original start datetime or time field.

Aggregate Functions

Using aggregate functions, you can perform calculations on a set of values or a single value, and then return a single value for an expression.

Sum

Argument DataType Cardinality Description
Items Numeric N Items to sum.
Return 1 The sum values for all of the items (The return value is the same data type as the items data type.

Avg

Argument DataType Cardinality Description
Items Numeric N Items to average (In Report Builder, Avg is listed on the Function tab as AVERAGE).
Return 1 The average of non-null values of the Items (The data type is Decimal if the items are Decimal or Integer, otherwise, Float).

Max

Argument DataType Cardinality Description
Items DateTime, Integer, Decimal, Float, or String N Items sorted to determine the maximum.
Return 1 The maximum of non-null values of the item (The return value is the same data type as the items data type).

Min

Argument DataType Cardinality Description
Items DateTime, Integer, Decimal, Float, or String N Items sorted to determine the minimum.
Return 1 The minimum of non-null values of the Items (The return value us the same data type as the items data type).

Count

Argument DataType Cardinality Description
Items Any N Items to count.
Return Integer 1 The count of non-null values of the Items.

Count Distinct

Argument DataType Cardinality Description
Items Any N Items to count (The data type of the items cannot be EntityKey).
Return Integer 1 The count of distinct non-null values of the Items.

StDev

Argument DataType Cardinality Description
Items Numeric N Items used to determine the standard deviation.
Return Float 1 The standard deviation of non-null values of the Items.

StDevP

Argument DataType Cardinality Description
Items Numeric N Items used to determine the population standard deviation.
Return Float 1 The population standard deviation of non-null values of the Items.

Float

Argument DataType Cardinality Description
Items Numeric N Items used to determine the variance.
Return Float 1 The variance of non-null values of the Items.

VarP

Argument DataType Cardinality Description
Items Numeric N Items used to determine the population variance.
Return Float 1 The population of variance of non-null values of the Items.

Other Functions

In addition, the following functions are used in Semantic Model Definition Language

Filter

Argument DataType Cardinality Description
Filter Items Any N The values to be filtered. To filter items, use the Filter dialog box.
Filter Condition Boolean 1 Indicates whether to include the corresponding instance.
Return N The returned value data type is the same as the Filter Items.

In

Argument DataType Cardinality Description
Item Boolean, DateTime, Integer, Decimal, Float, String, or EntityKey 1 The item to check for set membership.
Set N This must be a Literal expression with no Path. The Item and the Set must have the same data type.
Return Boolean 1 Indicates whether the item is in the set.

If

Argument DataType Cardinality Description
Condition Boolean 1 Condition to test.
TrueCase Any 1 Value to return if the condition is true. If the condition is false, TrueCase is not evaluated. Cannot be an EntityKey data type.
FalseCase Any 1 Value to return if the condition is false. FalseCase must have the same value type as TrueCase. If the condition is true, FalseCase is not evaluated.
Return 1 The type must be the same as TrueCase.

Switch

Argument DataType Cardinality Description
Condition1 Boolean 1 Condition to test. Switch can have zero or more additional Condition/Value pairs.
Value1 Any 1 Value to return if condition1 is true.
Condition/N Boolean 1 Condition to test. Not evaluated if any earlier condition is true.
Value/N Boolean 1 Value to return if Condition/N is true. It must have the same data type as Value1. Not evaluated if Condition/N is not evaluated or if Condition/N is false.
Return Boolean 1 Type is the same as the Value1. Returns Null if all conditions are false.

Evaluate

Argument DataType Cardinality Description
Expression Any N The expression to evaluate. This function is used for controlling the entity context in which an expression is evaluated.
Return N Type is the same as the expression.

For example, the following expression averages the prices for each distinct product the customer ordered: Avg([customer→order→product]Price).

The following expression averages the prices (obtained from the product entity) for each order the customer placed: Avg([customer→order]Evaluate([order→product]Price)).

There are multiple points along a path where evaluation location can control distinctness of an aggregate, multiple independent uses of Evaluate may be possible.

The following is an example of multiple independent uses of Evaluate in a model where each product can be produced by multiple manufacturers: Average([customer→order]Evaluate([order→product]Evaluate([product→manufacturer→city]population))).

Aggregate

Argument DataType Cardinality Description
Expression Any N The aggregate expression to evaluate. This function is used to control the entity context in which an aggregation is evaluated.
Return 1 Type is the same as the expression.

For example, consider a TotalSales attribute defined on an Order entity.

The expression to show the total sales within the context of an order is simply: TotalSales.

The expression to calculate the total sales within the context of a customer would be: Aggregate([customer→order]TotalSales).

The expression argument must contain a non-anchored expression or one or more nested passthrough functions (which must take any data type), where the innermost passthrough argument has a non-anchored ExpressionNode.

For example, Aggregate([customer→order]Filter([order→product]Sum(UnitPrice),=(Shipped, “True”)).

    • Related Articles

    • Expressions, Functions, Formulas, Logical Operators

      Functions, Formulas and Logical Operators Arkieva allows users to add expressions to a variety of documents like Workbenches, Quick Reports, as well as define measures within the Arkieva Setup Manager. When defining the expressions, mathematical ...

    • Expressions, Functions, Formulas, Logical Operators

      Functions, Formulas and Logical Operators Arkieva allows users to add expressions to a variety of documents like Workbenches, Quick Reports, as well as define measures within the Arkieva Setup Manager. When defining the expressions, mathematical ...

    • KPIs and Quick Reports

      Building off Session #31, learn how to utilize the built-in functions to create KPIs within quick reports. {` `}

    • Data Canvas

      Introduction Data Canvas is an easy way to edit table and data source data in a Grid and Spreadsheet view. Select the source of the data, Table Editor or Data Source, to create a Data Canvas. A table or a data source must first be created before the ...

    • General Navigation of Arkieva

      Overview of the various navigational commands in Arkieva. {` `} Launching Arkieva Splash Screen Arkieva is installed to a web server and launched from the client intranet (Internet Explorer is highly recommended). The Arkieva version number, build ...