Generic Functions in QPR ProcessAnalyzer: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
(345 intermediate revisions by 3 users not shown)
Line 1: Line 1:
Generic Properties and Functions are available for all objects.
== Aggregation functions ==
Following aggregation functions are available:
* '''Average''': Average of the items (sum of items divided by count of items). Can be used for numbers, [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#DateTime|DateTimes]] and [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#TimeSpan|Timespans]].
* '''Count''': Number of items. Can be used for any type of data.
* '''Median''': Median, i.e. the middle value of the sorted data. If there are even number of items, the average of the two middle items. Can be used for numbers, [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#DateTime|DateTimes]] and [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#TimeSpan|Timespans]].
* '''Min''': Lowest/first value in the data. Can be used for data that can be sorted.
* '''Max''': Highest/last value in the data. Can be used for data that can be sorted.
*'''Percentile''': Percentile value. Can be used for numbers, [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#DateTime|DateTimes]] and [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#TimeSpan|Timespans]].
*'''Stdev''': Sample standard deviation. Can be calculated for numbers, [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#DateTime|DateTimes]] and [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#TimeSpan|Timespans]].
*'''Stdevp''': Population standard deviation. Can be calculated for numbers, [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#DateTime|DateTimes]] and [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#TimeSpan|Timespans]].
* '''Sum''': Sum of the items. Can be used for numbers and [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#TimeSpan|Timespans]].
* '''StringJoin''': Joins array of strings together. The first parameter is a string that is placed between the joined strings.
*'''Var''': Sample variance. Can be used for numbers.
*'''Varp''': Population variance. Can be used for numbers.


=== Generic Properties ===
Examples:
<pre>
Sum([3, 2, 4])
Returns: 9
 
Count([[1, 2], [3, 4, 5]])
Returns: [2, 3]
 
Sum([[1, 2], [3, 4, 5]])
Returns: [3, 12]
 
Percentile([1,2,3,4,5], 0.75)
Return: 4
 
Sum([])
Returns: null
 
StringJoin(", ", ["one", "two", "three"])
Returns: one, two, three
</pre>
 
When aggregating numerical values, ''null'' values are treated as zeros. Nulls can be removed with ''RemoveNulls'' function. In the Min and Max functions, nulls are ignored. Null values can be removed before aggregating (the following example shows how).
<pre>
Average([1, 5, null])
Returns: 2
 
Average(RemoveNulls([1, 5, null])
Returns: 3
</pre>
 
Aggregation operations are performed to the leaf level. i.e. the deepest level in the hierarchy. When aggregating, the leaf level arrays are replaced by the aggregated values, and thus the depth of the hierarchy decreases by one. In addition to the aggregation functions, functions that modify the contents of leaf arrays ('''OrderBy''', '''Distinct''', ...), the operation will be performed separately for every leaf array.
 
<pre>
OrderByValue([[4, 3], [2, 1]])
Returns: [[3, 4], [1, 2]]
</pre>
 
== Mathematical functions ==


{| class="wikitable"
{| class="wikitable"
!'''Property'''
!'''Function'''
!'''Parameters'''
! '''Description'''
! '''Description'''
|-
|-
||_
||Abs (Number)
||Number
||
||
Refers to the current context.
Calculates the absolute value of a specified number. Examples:
<pre>
Abs(4)
Returns: 4
 
Abs(-2.7)
Returns: 2.7
 
Abs(0)
Returns: 0
</pre>
|-
|-
||_empty 
||Acos (Number)
||Number
||
||
Returns an object which represent a non-existent value. Textual representation of this value is "EMPTY".
Calculates the angle whose cosine is the specified number. The parameter is a number representing a cosine, which must be between -1 and 1. Examples:
<pre>
Acos(0.3584)
Returns: 1.204242852965772
 
Acos(-0.3584)
Returns: 1.9373498006240213
</pre>
|-
|-
||_remove 
||ArgMax (Object)
||
# Array or hierarchical object
# Value expression
||
||
Returns an object which represent a value that should automatically be recursively pruned out of the resulting hierarchy object when processing chained expressions. If all the child values of one context object used in chaining expressions return this object, the root object itself will also be pruned out of the resulting hierarchy object.
Returns those objects in the array giving maximum values for the given expression when evaluated in the object's context. Examples:
<pre>
ArgMax([5, 1, 6, 4, 3, 3, 6, 5, 4, 1, 1], _)
Returns: [6, 6]


Examples:
ArgMax(EventLogById(1).Cases, Duration)
Returns: An array of cases having the longest duration.
</pre>
|-
||ArgMin (Object)
||
# Array or hierarchical object
# Value expression
||
Returns those objects in the array giving minimum values for the given expression when evaluated in the object's context. Examples:
<pre>
<pre>
Both:
ArgMin([5, 1, 6, 4, 3, 3, 6, 5, 4, 1, 1], _)
[1,2]._remove
Returns: [1, 1, 1]
[1,2].Where(_==3, _remove)
Return EMPTY


whereas
ArgMin(EventLogById(1).Cases, Duration)
Returns: An array of cases having the shortest duration.
</pre>
|-
||Asin (Number)
||Number
||
Calculates the angle whose sine is the specified number. The parameter is a number representing a sine, which must be between -1 and 1. Examples:
<pre>
Asin(-0.3584)
Returns: 0.36655347382912462


[1,2].Where(_==3, _empty)
Asin(+0.3584)
[1,2].Where(_==3)
Returns: -0.36655347382912462
[1,2]._empty
</pre>
Returns an empty collection (collection of length 0).
|-
||Atan (Number)
||Number
||
Calculates the angle whose tangent is the specified number. Examples:
<pre>
Atan(1)
Returns: 0.78539816339744828


For("i", 0, i < 10, i + 1, i).Where(_ % 2 == 0)
Atan(-1)
For("i", 0, i < 10, i + 1, i).If(_ % 2 == 0, _, _remove)
Returns: -0.78539816339744828
Both return:
[0, 2, 4, 6, 8]
</pre>
</pre>
|-
|-
||CalcId (string)
||Ceiling (Integer)
||
Number
||
||
Returns an id that is unique between all QPR ProcessAnalyzer objects. CalcId is remains the same at least for the duration of the expression calculation. It is possible that CalcId for an object is different in the next calculation. CalcId is EMPTY for other than QPR ProcessAnalyzer objects.
Returns the smallest integer that is greater than or equal to the specified number. Example:
<pre>
Ceiling(1.3)
Returns: 2
</pre>
|-
|-
||Models (Model*)
||Cos (Number)
||All [[QPR ProcessAnalyzer Expressions#Model|Model]] objects in the QPR ProcessAnalyzer server (that the user have rights).
||Number
||
Calculates the cosine of the specified angle measured in radians. Examples:
<pre>
Cos(1)
Returns: 0.54030230586813977
 
Cos(-1)
Returns: 0.54030230586813977
</pre>
|-
|-
||Now (DateTime)  
||Exp (Number)
||[[QPR ProcessAnalyzer Expressions#DateTime|DateTime]] object representing the current date and time.
||Number
||
Calculates the e raised to the specified power. Examples:
<pre>
Exp(1)
Returns: 2.7182818284590451
 
Exp(-1)
Returns: 0.36787944117144233
</pre>
|-
|-
||Null 
||Floor (Integer)
||
||
Returns a special null value. Null value is similar to EMPTY value, except null values can also be recursed. In some cases null values can be converted into numbers in which case they act as 0.
Number
||
Returns the largest integer that is less than or equal to the specified number. Example:
<pre>
Floor(1.9)
Returns: 1
</pre>
|-
|-
||Projects (Project*)
||Hash (String)
||All Projects in the QPR ProcessAnalyzer server (that the user have rights).
||String to hash
|}
||
Returns 11 characters long hash value for the given string. There can be following characters in the hash: A-Za-z0-9+/. For null or empty string, "AAAAAAAAAAA" is returned.
 
The hashing is a repeatable operation, i.e., the same input string always returns the same hash value. Note also that hashing lead to collisions, i.e., two different input strings may give the same hash value.


=== Aggregation Functions ===
The hash is calculated using Murmur3 algorithm (https://blog.teamleadnet.com/2012/08/murmurhash3-ultra-fast-hash-algorithm.html) and the first 64 bits of the 128-bit hash value is returned encoded as base64url without padding (RFC 4648 §5).
Aggregation functions are performed by default to the leaf level (the deepest level). Aggregation means that leaf level arrays are replaced by the aggregated values, and thus the depth of the hierarchy decreases by one. There are following aggregation functions available: '''Average''', '''Count''', '''Min''', '''Max''' and '''Sum'''.


Examples:
Examples:
<pre>
<pre>
Count([[1, 2], [3, 4, 5]])
Hash("abcdef")
Returns: [2, 3]
Returns: 3sqiubzVXxA
 
</pre>
Sum([[1, 2], [3, 4, 5]])
|-
Returns: [3, 12]
||IEEERemainder (Number)
 
||
# Dividend (Number)
# Divisor (Number)
||
Calculates the remainder resulting from the division of a specified number by another specified number. Uses formula:
<pre>
dividend - (divisor * Round(dividend / divisor))
</pre>
</pre>


In addition to the aggregation functions, functions that modify the contents of leaf arrays ('''OrderBy''', '''Distinct''', ...), the operation will be performed separately for every leaf array.
Note that the formula is different than in the % operator, which uses formula:
 
<pre>
<pre>
OrderByValue([[4, 3], [2, 1]])
(Abs(dividend) - (Abs(divisor) * (Floor(Math.Abs(dividend) / Abs(divisor))))) * Sign(dividend)
Returns: [[3, 4], [1, 2]]
</pre>
</pre>


=== Mathematical functions ===
Examples:
<pre>
IEEERemainder(26, 4)
Returns: 2


{| class="wikitable"
IEEERemainder(0, 0)
!'''Function'''
Returns: NaN
!'''Parameters'''
</pre>
! '''Description'''
|-
|-
||Ceiling (Integer)
||Log (Number)
||
||
# Number (Float)
# Number
# Logarithm base (Number)
||
||
Returns the smallest integer greater than or equal to the specified number.
Calculates the logarithm of a specified number. If the second parameter is not provided, it's the natural logarithm (base e). The second parameter can be used to define another base for the logarithm. Examples:
<pre>
Log(5)
Returns: 1.6094379124341003
 
Log(10, 2)
Returns: 3.3219280948873626
</pre>
|-
|-
||Floor (Integer)
||Log10 (Number)
||
||
# Number (Float)
Number
||
||
Returns the largest integer less than or equal to the specified number.
Calculates the base 10 logarithm of a specified number. Examples:
<pre>
Log10(5)
Returns: 0.69897000433601886
 
Log10(-5)
Returns: NaN
</pre>
|-
|-
||Round (Float)
||Pow (Number)
||
||
# Number to be rounded (Float)
# Base number
# Number of decimals (Integer)
# Exponent number
||
||
Rounds a value to the nearest integer or to the specified number of fractional digits.
Calculate a specified number raised to the specified power. Examples:
|}
<pre>
Pow(5, 3)
Returns: 125


=== Ordering functions ===
Pow(-5, 3)
Returns: -125
</pre>
|-
||Random (Number)
||(none)
||Returns a random number value between 0 and 1, where 0 is included, but 1 is not.


{| class="wikitable"
Example:
!'''Function'''
<pre>
! '''Parameters'''
Random()
! '''Description'''
Returns: 0.1243234243 (for example)
</pre>
|-
|-
||OrderBy (array)
||Round (Float)
||
||
# Array to order
Number of decimals (Integer)
# Order expression
||
||
Orders the given array using values from the given order expression. The order expression is evaluated once for each item in the array. The order expression supports all atomic (=not collection) primitive value types.
For numbers, rounds the number to the specified number of decimal. Number of decimals can also be negative, in which case the number is rounded to the nearest tens, hundreds, thousands, etc.
 
For DateTimes, rounds given date time by given time span or given number of seconds.
 
Note that the number and DateTime to be rounded needs to be provided as a context object (not as a parameter).


Examples:
Examples:
<pre>
<pre>
OrderBy(["a", "x", "b", "z", "n", "l", "a"], _)
(1.254).Round(1)
Return:
Returns: 1.3
["a", "a", "b", "l", "n", "x", "z"]
 
(162.111111).Round(-2)
Returns: 200
 
DateTime(2017, 1, 2, 3, 4, 5).Round(10)
Returns: DateTime(2017, 1, 2, 3, 4, 10)
 
DateTime(2017, 1, 2, 3, 4, 5).Round(TimeSpan(1))
Returns: DateTime(2017, 1, 2)
</pre>
|-
||Sign (Number)
||
Number
||
Calculates an integer that indicates the sign of a number, either -1, 0 or 1. Examples:
<pre>
Sign(10)
Returns: 1


OrderBy([9,8,7,6,5,4,3,2,1], _%3)
Sign(0)
Returns:
Returns: 0
[9,6,3,7,4,1,8,5,2]


OrderBy([9,8,7,6,5,4,3,2,1], _%3 + _/30)
Sign(10)
Returns:
Returns: -1
[3,6,9,1,4,7,2,5,8]
</pre>
</pre>
|-
|-
||OrderByDescending (array)
||Sin (Number)
||
Number
||
||
# Array to order
Calculates the sine of the specified angle. Examples:
# Order expression
<pre>
||Result is same as in the OrderBy function, except the order is reverse.
Sin(3.14)
Returns: 0.0015926529164868282
 
Sin(-3.14)
Returns: -0.0015926529164868282
</pre>
|-
|-
||OrderByValue (array)
||Sqrt (Number)
||
||
# Array to order
Number
||
||
Orders the given array using the natural order of items, for example numbers it's the increasing order, and for strings it's text ordering.
Calculates the square root of a specified number. Examples:
 
<pre>
<pre>
OrderByValue(["a", "x", "b", "z", "n", "l", "a"])
Sqrt(81)
Return:
Returns: 9
["a", "a", "b", "l", "n", "x", "z"]


Sqrt(0.9)
Returns: 0.3
</pre>
</pre>
|-
|-
||OrderByValueDescending (array)
||Tan (Number)
||
||
# Array to order
Number
||
||
Result is same as in the OrderByValue function, except the order is reverse.
Calculates the tangent of the specified angle. Examples:
 
<pre>
Tan(1)
Returns: 1.5574077246549023
 
Tan(-1)
Returns: -1.5574077246549023
</pre>
|}
|}


=== Recursion functions ===
== Conversion functions ==


{| class="wikitable"
{| class="wikitable"
!'''Function'''
!'''Function'''
! '''Parameters'''
!'''Parameters'''
! '''Description'''
! '''Description'''
|-
|-
 
||ToFloat (Number)
||
Object to convert
||Converts the given object to a decimal number.
|-
|-
||Recurse
||ToInteger (Integer)
||
||
# Expression to call recursively
Object to convert
# Stop condition expression
# Maximum depth (Integer)
||
||
Evaluates the given expression recursively until given condition or recursion depth is met. The function returns all the traversed objects in a single array. When the stop condition expression evaluates to false, it will stop the current recursion without including the false evaluated object into the result. Default stop condition is '''!IsNull(_)'''. The default maximum depth is 1000.
Converts the given object into an integer. If the object can not be converted into an integer, an exception will be thrown. If the object as a decimal number, it will also be rounded to the nearest integer.


Examples:
Examples:
<pre>
<pre>
(1).Recurse(_ + 1, _ < 5)
ToInteger(1.234)
Returns: [1, 2, 3, 4]
Returns: 1
 
ToInteger(-1.5)
Returns: -2
 
ToInteger(1313.6)
Returns: 1314


event.Recurse(NextInCase)
ToInteger(123456789012345678)
Returns: An array of all the events following given event inside the same case.
Returns: 123456789012345678


event.Recurse(NextInCase, Type.Name != "Invoice")
ToInteger("5")
Returns: An array of all the events following given event inside the same case until event whose type name is "Invoice", which will itself not be included into the result.
Returns: 5


event.Recurse(NextInCase, Type.Name != "Invoice", 2)
ToInteger("-5")
Returns: An array of all the events following given event inside the same case until event whose type name is "Invoice" or until the recursion depth of 2 has been reached, which will itself not be included into the result.
Returns: -5
</pre>
</pre>
|-
|-
||RecurseWithHierarchy
||ToString (String)
||
||
# Expression to call recursively
# Object to convert
# Stop condition expression
# Date/Number/Timespan format (String)
# Maximum depth (Integer)
||
||
Evaluates the given expression recursively until given condition or recursion depth is met. The function returns the traversed object hierarchy. When the stop condition expression evaluates to false, it will stop the current recursion without including the false evaluated object into the result. Default stop condition is '''!IsNull(_)'''. The default maximum depth is 1000.
Converts the given object to a string.
 
The optional second parameter defines formatting that is needed when converting the following data types:
* Date: https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings
* Number: https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings
* Timespan: https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-timespan-format-strings


Examples:
Examples:
<pre>
<pre>
[1,2].RecurseWithHierarchy([1,2], false, 2)
ToString(1) + " < " + ToString(2)
Returns: [1:[1:[1,2],2:[1,2]],2:[1:[1,2], 2:[1,2]]]
Returns: "1 < 2".
 
"Case=\'" + ToString(a) + "\'"
Returns: "Case='<string representation of object a>'"
 
ToString(DateTime(2017,1,2,3,4,5,6))
Returns: "2017-01-02T03:04:05"
 
ToString(DateTime(2017,1,2,3,4,5,6), "dd.MM.yyyy")
Returns "02.01.2017"


(1).RecurseWithHierarchy(_ + 1, _ < 5)
ToString(1.2, "0.00")
Returns: 1:[2:[3:[4]]]
Returns "1.20"


RemoveLeaves(eventLog.Flows:From.Where(IsNull(_))).To.RecurseWithHierarchy(OutgoingFlows.To, !IsNull(_), 2)
ToString(TimeSpan(16,4,3,17,250), "%d")
Returns: A hierarchy consisting of all the starter events of given event log and recursively all the event types reachable from them via flows until depth of 2 is reached.
Returns "16"
</pre>
</pre>
|}
== Data source connectors ==
{| class="wikitable"
!'''Function'''
!'''Parameters'''
! '''Description'''
|-
|-
||RecursiveFind
||CallWebService (Dictionary)
||Dictionary of parameters
||
||
# Expression to call recursively
CallWebService is a generic function for any kind of http requests. Support natively REST-style web services by automatically formatting and parsing json in the request and response, so dictionary/array objects are used without converting them to strings (see examples  below). If using xml-based web services, formatting and parsing of xml needs to be taken care of manually.
# Find condition expression
 
# Stop condition expression
The function takes as a dictionary of following parameters:
# Maximum depth (Integer)
 
# Continue after finding (Boolean)
*'''Address''' (string): Url address to connect to.
||
*'''Method''' (string): Http method to use, as one of the following: GET (default), POST, PUT, DELETE.
Evaluates given expression recursively until given condition or recursion depth is met. The function collects all the traversed objects that match the given find expression along the way. When the find condition expression evaluates to true for the current object, it causes the following:
*'''Body''' (string/dictionary/array): The http request body. In case the request content type is application/json, dictionary/array can be used directly without string conversion.
* Current object is added to the result array returned by the function call
*'''Content-Type''' (string): Defines the Content-Type http request header. If content type is application/x-www-form-urlencoded, form url encoding is applied for the request body.
* If continue after finding is false, the recursion will not be continued on this branch
*'''Timeout''' (integer): Time to wait before the request times out, in case no response is received earlier. Defined as integer in milliseconds. By default 60000 milliseconds.
*'''Headers''' (dictionary): Key-value pairs of HTTP request headers.
*'''QueryParameters''' (dictionary): Key-value pairs of query parameters that are added to the url. Proper encoding of the url is taken care of by the function.
*'''DefaultNetworkCredentials''' (boolean): When true, uses the default network credentials for the call. When false, the default network credentials are not used. The default network credentials can be used only if in the server configuration AllowForwardingNetworkCredentials setting is enabled.
*'''Encoding''' (string): Defines the body encoding to use. Default value is UTF8. The supported options are listed in https://msdn.microsoft.com/en-us/library/system.text.encoding%28v=vs.110%29.aspx.
 
CallWebService returns a dictionary with properties:
* '''Response''' (string/dictionary/array): Response data as string, or if content type is application/json, as dictionary/array object.
* '''StatusCode''' (integer): Http status code.
* '''Headers''' (dictionary): All http response headers.


When the stop condition expression evaluates to false, it will stop the current recursion without including the false evaluated object into the result. Default stop condition is '''!IsNull(_)'''. The default maximum depth is 1000.
The Content-Type http request header is set as follows: If the Content-Type parameter is provided, that is used. If Content-Type is not provided, and body is dictionary/array, request Content-Type is set to application/json. Otherwise no Content-Type header is set.


Continue after finding tells should the recursion be continued after a match has been found in the current branch.
If the Content-Type parameter is application/x-www-form-urlencoded, body is expected to be a dictionary where the name/values pairs are encoded using FormUrlEncodedContent method (see examples below).


Examples:
Examples:
Get a webpage and check the status code of the response:
<pre>
let resp = CallWebService(#{
  "Address": "https://www.google.com/search?q=QPR+ProcessAnalyzer"
});
resp.StatusCode;
</pre>
Get the server version in a QPR ProcessAnalyzer environment:
<pre>
let resp = CallWebService(#{
  "Address": "https://processanalyzer.onqpr.com/qprpa/api/serverinfo"
});
resp.Response.serverVersion;
</pre>
Authenticate to QPR ProcessAnalyzer (to get an access_token) and call expression query (note the Content-Type required by the token request):
<pre>
let server = "http://processanalyzer.onqpr.com/qprpa";
let resp = CallWebService(#{
  "Address": server + "/token",
  "Method": "POST",
  "Content-Type": "application/x-www-form-urlencoded",
  "Body": #{
    "grant_type": "password",
    "username": "qpr",
    "password": "demo"
  }
});
resp = CallWebService(#{
  "Address": server + "/api/expression/query",
  "Method": "POST",
  "Content-Type": "application/json",
  "Headers": #{ "Authorization": "Bearer " + resp.Response.access_token },
  "Timeout": 120000,
  "Body": #{
    "Root": "Models",
    "Dimensions": null,
    "Values": [ #{"Name": "Name", "Expression": "Name"} ],
    "Ordering":[ #{"Name": "Name", "Direction": "Ascending"} ]
  }
});
resp.Response[0].Name
</pre>
Upload contents of datatable id 321 to using Web API to a datatable id 123:
<pre>
<pre>
(1).RecursiveFind(_ + 1, _ == 100)
CallWebService(#{
Returns: 100
  "Address": "https://processanalyzer.onqpr.com/qprpa/api/importfile?importMode=datatable&fileType=csv&objectId=123&append=true&csvTimeFormat=yyyy-MM-dd%20HH%3Amm%3Ass,fff",
  "Method": "POST",
  "Body": DatatableById(321).SqlDataFrame.Collect().ToCsv(),
  "Headers": #{ "Authorization": "Bearer " + resp.Response.access_token },
  "Timeout": 6000000
});
</pre>
|-
||<span id="OpenAIChatCompletion">OpenAIChatCompletion</span> (Dictionary)
||Request dictionary
||Calls OpenAI's Chat Completions API (https://platform.openai.com/docs/api-reference/chat) to generate natural language responses to user prompts. Both the request and response is a dictionary following the Chat Completions API specification. The ''messages'' parameter is the only mandatory fields in the request. The ''model'' parameter defines the OpenAI LLM model to use, and if not defined, model specified by the [[PA_Configuration_database_table#OpenAIDefaultModelName|OpenAIDefaultModelName]] setting will be used.


eventLog.Cases:GetAt(0, Events).RecursiveFind(NextInCase, Organization=="Finance", !IsNull(_))
To use the function, the OpenAi API key needs to be configured in [[PA_Configuration_database_table#General_Settings|OpenAIAPIKey]] configuration table.
Returns: For each case, returns the first (by time) event whose organization equals to "Finance".


eventLog.Cases:Events.Where(Organization=="Finance")
Example: Response to a user request:
eventLog.Cases:GetAt(0, Events).RecursiveFind(NextInCase, Organization=="Finance", true, true)
<pre>
Returns: Both return for each case all events whose organization equals to "Finance".
let response = OpenAIChatCompletion(#{
  "model": "gpt-3.5-turbo",
  "messages": [
    #{
      "role": "system",
      "content": "You are a process mining expert making intelligent suggestions how to improve processes."
    },
    #{
      "role": "user",
      "content": "Give me three suggestions how to improve a sales process with bottlenecks."
    }
  ]
});
response["choices"][0]["message"]["content"];
</pre>
</pre>
|}
|}


=== Loop functions ===
== Miscellaneous functions ==
 
{| class="wikitable"
{| class="wikitable"
!'''Function'''
!'''Function'''
Line 246: Line 550:
! '''Description'''
! '''Description'''
|-
|-
||For
||Where
||
||
# Iterated variable name (String)
Condition expression
# Initial value for iterated property (object)
# Iteration condition expression
# Next iteration step expression
# Expression to calculate iterated items
||
||
Iterates the given expression until the given condition is met, and returns the results of the iterated expressions for every iteration as an array.
Returns the context object if the given expression evaluates to true.


Examples:
Examples:
<pre>
<pre>
For("i", 0, i < 4, i + 1, i)
[1,2,3,4].Where(_>2)
Returns: [0, 1, 2, 3]
Returns: [3,4]


For("x", 0, x < 3, x + 1, For("y", 0, y < 3, y + 1, StringJoin(",", [x, y]))
EventLogById(1).Cases.Where(Duration > TimeSpan(10))
Returns: [["0,0", "0,1", "0,2"], ["1,0", "1,1", "1,2"], ["2,0", "2,1", "2,2"]]
Returns all cases where duration is more than 10 days (in eventlog id 1).


For("i", 0, i < 4, i + 1, DateTime(2010 + i))
EventLogById(1).Events.Where(Timestamp < DateTime(2020, 1,1))
Returns: [DateTime(2010), DateTime(2011), DateTime(2012), DateTime(2013)]
Returns all events which occurred before 1.1.2020 (in eventlog id 1).
 
For("str", "a", str != "aaaaa", str + "a", str)
Returns: ["a", "aa", "aaa", "aaaa"]
</pre>
</pre>
|-
|-
||ForEach
||<span id="If">If</span>
||
||
# Variable to repeat (String)
# Condition expression
# Array to repeat
# True expression
# Expression to calculate repeated items
# False expression
||
||
Repeats the given expression as many times there are items in the given array. Item in the array is available as the given variable in the expression.
If the first parameter is true, returns the value of the second parameter. Otherwise returns the value of the third parameter. The function always evaluates only either the second or third parameter, but never both.
 
Note that the function needs to starts with a capital letter, because otherwise it's interpreted as the [[QPR_ProcessAnalyzer_Expressions#Conditional_operator_.28if.29|if operator]].
 
Examples:
Examples:
<pre>
<pre>
ForEach("i", [1,2,3], "Value is: " + i)
If(Now.Second % 2 == 0, "Even second", "Odd second")
Returns:
Returns:
Value is: 1
"Event second" or "Odd second" depending on the time of the evaluation.
Value is: 2
Value is: 3


For("i", 0, i < 10, i + 1, i).If(_ % 2 == 0, _, _remove)
Returns:
Returns:
aa
[0, 2, 4, 6, 8]
bb
</pre>
cc
|-
||<span id="RemoveNulls">RemoveNulls (Array)</span>
||
Array
||
Removes all the supported null values from given hierarchical object. Never removes arrays containing removed null values. Returns given object with null values removed.


Results: Creates expression variable variables like "c<casename>" for every case in the model.
Examples:
<pre>
RemoveNulls([1, [], [null], [2, _remove, [_empty, 3, 4], _empty, null, 5]])
Returns: [1, [], [], [2, [3, 4], 5]]


ForEach("item", EventLogById(1).Cases, Let("c" + item.Name, item))
RemoveNulls(["foo": null, "bar": 2, "poof": [1, _empty, 3]])
ForEach("myVariable", ["a", "b", "c"], myVariable + myVariable)
Returns: [
  "foo": 0,
  "bar": 2,
  "poof": [ 1, 3 ]
]
</pre>
</pre>
|-
|-
||NumberRange
||Catch
||
||
# Start (Number)
# Expression to calculate
# End (Number)
# Result if exception (Object)
# Interval (Number)
||
||
Creates an array of numbers within given range with the given interval. Interval parameter is optional, and by default it is one.
Calculates the given expression and if any exceptions are thrown during the calculation, catches that exception and returns the given result. Note that this function does not catch any syntactical errors.


Examples:
Examples:
<pre>
<pre>
NumberRange(1, 3)
Catch(1, 1234)
Returns: [1, 2, 3]
Returns: 1
 
Catch(undefined, 1234)
Returns: 1234


NumberRange(1, 3, 0.5)
Catch([1,2].undefined, 1234)
Returns: [1, 1.5, 2, 2.5, 3]
Returns: 1234


NumberRange(1, 3, 0.8)
Catch(EventLogById(-1), 1234)
Returns: [1, 1.8, 2.6]
Returns: 1234
</pre>
</pre>
|-
|-
||Repeat
||AsParallel
||
||
# Number of times to repeat (Integer)
Additional parameters
# Expression to repeat
||
||
Repeats the defined expression the defined number of times.
Performs all the following chaining operations in parallel to improve performance. Items are divided into parts which size is determined by the ParallelTaskSize parameter, and each part is executed as an independent task in parallel. Parallel execution has a certain cost, so it might not be optimal to run each item as a separate task (increase ParallelTaskSize to decrease number of tasks). On the other hand, to large ParallelTaskSize leads to too few parallel tasks, and then all processing capacity is not used.
 
As a parameter, takes a dictionary that accepts optional property '''ParallelTaskSize''', which is the size of the segments the root array will be split into. If the length of the input array is not divisible by ParallelTaskSize, the last segment will have less items than this configured value. The default size is 1.
 
The AsParallel function call doesn't need to be part of the chaining, and thus it can be called before the chaining.
 
Examples:
Examples:
<pre>
<pre>
Repeat(3, "Repeat me!")
Run several ODBC queries simultaneously:
Returns:
let regions = ["New York", "Los Angeles", "Dallas", "London"];
"Repeat me!"
AsParallel(["ParallelTaskSize": 1]);
"Repeat me!"
regions.ImportOdbc("...", "SELECT * FROM [Data] Where Region = '" + _ + "'")
"Repeat me!"
 
Run a complex filtering operation for cases:
Cases.AsParallel(["ParallelTaskSize": 1000]).Where(...)
 
Sum(Sum(([NumberRange(1, 100)].AsParallel(["ParallelTaskSize": 1]).For("i", 0, i < 100000, i + 1, i))[0]))
Returns: 499995000000
The same expression without parallel processing:
Sum(Sum(NumberRange(1, 100).For("i", 0, i < 100000, i + 1, i)))
Returns: 499995000000
 
Sum([NumberRange(1, 100)].AsParallel(["ParallelTaskSize": 1]).WriteLog(_))
Returns: 5050
In addition, outputs all the numbers from 1 to 100 into log file in the order in which the tasks were executed.
 
The previous example can also be written like this:
AsParallel(["ParallelTaskSize": 1]);
Sum(NumberRange(1, 100).WriteLog(_));
Returns: 5050
 
Count((([el.Cases].AsParallel(["ParallelTaskSize": 1000]):AnalyzeConformance(cm))[0]).Where(_==null))
Returns the number of cases conforming to a design/conformance model cm.
The same expression without parallel processing:
Count((el.Cases:AnalyzeConformance(cm)).Where(_==null))
 
AsParallel(["ParallelTaskSize": 1]);
el.Cases.Events.Type.Name
Returns a hierarchical object having cases of given event log (el) as root objects and array of event type names of the events in each case in the order of occurrence.
The expressions related to every case in the returned hierarchical object are processed in a separate independent task (thus, each task will process "Events.Type.Name" expression in a separate task where the root object is a case).


Repeat(1, 5)
[[123, 456, 789]].AsParallel(["ParallelTaskSize": 1]).ScriptById(_).Run()
Returns
Runs scripts with id's 123, 456 and 789 simultaneously.
5
</pre>
</pre>
|-
|-
||TimeRange
||IsNull (Boolean)
||
||
# Start (DateTime)
Value to test (Object)
# End (DateTime)
# Interval (Timespan)
||
||
Generates a timestamp array starting from the start timestamp with the defined interval, and ending when the end timestamp is reached. Note that this function only creates timestamps with equal durations, so it's not possible to e.g. create timestamps for each month (to do that, you can use the loops).
Tests whether given object is null, _empty or _remove. Returns ''true'' if it is any of those. If given a hierarchical object, applies the function as described in at the leaf level.
 
Examples:
<pre>
<pre>
Generate datetimes starting from Monday 2017-01-01 and ending to Monday 2017-12-31 including all Mondays between them:
ForEach("item", [1, "a", null, _empty, _remove], IsNull(item))
Timerange(Datetime(2018,1,1), Datetime(2018,1,1), Timespan(7))
Returns: [false, false, true, true, true]
 
IsNull(["foo":[null,"a"], "bar":[2,null]])
Returns: [
  HierarchicalArray("foo", [true, false]),
  HierarchicalArray("bar", [false, true])
]
</pre>
</pre>
|}
|-
||IsNullTop (Boolean)
||
Object to test (Object)
||
Tests whether given object is null, _empty or _remove. Returns ''true'' if it is any of those. The function does not aggregate values in hierarchical objects.


=== Array functions ===
Examples:
<pre>
ForEach("item", [1, "a", null, _empty, _remove], IsNullTop(item))
Returns: [false, false, true, true, true]


{| class="wikitable"
IsNullTop(["foo":[null,"a"], "bar":[2,null]])
!'''Function'''
Returns: false
!'''Parameters'''
</pre>
! '''Description'''
|-
|-
||Array
||GroupBy
||
||
# Item 1 (Object)
# Array to group
# Item 2 (Object)
# Group by expressions
# ...
||
||
Creates a new array where the provided parameters are items of the array. There can be any number of parameters. Note: arrays can also be created using '''[]''' syntax.
Groups given array by given expressions. Returns the given array splitted into groups in a way that each specified group expression creates one level of hierarchical arrays having the root object the same as the group expression result.


Examples:
Examples:
<pre>
<pre>
Array(1, 2, 3)
GroupBy([1,2,2,3,3,4,5,5,4,4,4], _)
Returns: An array having three elements which are 1, 2 and 3.
Also following syntax can be used: [1, 2, 3]


Array()
Returns:
Returns: An empty array.
[[1:[1], 2:[2, 2], 3:[3, 3], 4:[4, 4, 4, 4], 5:[5,5]]
</pre>
</pre>
|-
|-
||Concat
||GroupByValue
||Array to group
||
||
# At least two arrays.
Groups all unique values in given array. Returns the given array in a format which has all the values in the original array only once as root objects and the root objects in the original array as contents of the arrays inside contexts.
# ...
 
||
In a way, this just switches root objects of hierarchical arrays to be the actual values and actual values to be root objects (without duplicates).
Concatenate multiple arrays together into one. Returns a single array which contains all the arrays given as parameter concatenated together in the same order they were given. If a parameter is not an array, it will be converted into an array having only the given element.
 
For every item in the array, the behavior is as follows:
* If the item is not an array, the value is used also as the root object of the item.
* If the item is an array, all the values are treated separately.
* If the item is a hierarchical array, its root object will be used as root object and each separate value treated separately as scalars (ending up being root objects of the resulting object).


Examples:
Examples:
<pre>
<pre>
Concat(1, [DateTime(2017), 3], [4, "foo"], "bar")
GroupByValue([1,2,2,3,3,4,5,5,4,4,4])
Returns: [1, DateTime(2017), 3, 4, "foo", "bar"]
Returns:
(The same as GroupBy([1,2,2,3,3,4,5,5,4,4,4], _))
[1:[1], 2:[2, 2], 3:[3, 3], 4:[4, 4, 4, 4], 5:[5,5]]
 
GroupByValue([1:["a","b"],2:["b","c"],3:["c"],4:["d","e"],5:["d","d","d"]])
Returns:
["a":[1], "b":[1, 2], "c":[2, 3], "d":[4, 5, 5, 5], "e":[4]]
 
Get count of item "2" in the array:
Let(\"groupped\", GroupByValue([1,2,4,1,4,2,3,3,2,4])); Count(GetValueOfContext(2, groupped))
Returns: 3
</pre>
</pre>
|-
|-
||GetAt
||Coalesce
||
||
# Index (Integer)
# Object to coalesce
# Array
# Result if Null
||
||
Returns element at given index from the beginning of the array. The first item has index zero. If given a hierarchical object, returns the element at given index of the root level array. Throws a calculation exception if the given index does not exist in given object or given object is not an array.
Returns the second parameter if the first parameter is null or empty. If the the given object is a hierarchical object, all the leaf level values are coalesced separately.


Examples:
Examples:
<pre>
<pre>
GetAt(0, [1,2,3])
Coalesce(0, 1)
Returns: 1
Returns: 0


GetAt(1, [[1, 2], [3, 4]])
Coalesce(null, 1)
Returns: [3, 4]
Coalesce(_empty, 1)
Coalesce(_remove, 1)
All return: 1


GetAt(1, [[[5, 6]], [[1, 2], [3, 4]]])
Coalesce([[null, 1], [null, null]], 3)
Returns: [[1, 2], [3, 4]]
Returns: [[3, 1], [3, 3]]


GetAt(1, GetAt(1, [[[5, 6]], [[1, 2], [3, 4]]]))
Coalesce([[null, 1], 2], 3)
Returns: [3, 4]
Returns: [[3, 1], null]
 
Coalesce([1, [null, 2], null], 3)
Returns: [1, [null, 2], 3]
</pre>
</pre>
|-
|-
||GetAtReverse
||Transpose
||
||
# Index (Integer)
Matrix to transpose
# Array
||
Transposes the given matrix.
 
Examples:
<pre>
Transpose([[1,2], [3,4], [1,4]])
Returns: [[1, 3, 1], [2, 4, 4]]
</pre>
|-
||GarbageCollection
||
||
Same as the GetAt function, except that the index is calculated from the end of the array.
||Calling this function performs a garbage collection in the QPR ProcessAnalyzer Server. The function returns the maximum generation before the garbage collection was run. This function is an experimental functionality, which purpose is to investigate the memory consumption and memory handling behavior of the QPR ProcessAnalyzer Server.
|-
|-
||In
||GetContext
||
||
# Object which to search
Hierarchical array
# Array where to find the object
# ...
||
||
Function tests whether the current context object is in given array. Returns true only if the context object is equal to at least one object in given array. This function only tests atomic objects and the top level of the given array.
Returns the context of given hierarchical array, i.e. list of keys in the object. If the given object is not an hierarchical array, returns ''null''.


Examples:
Examples:
<pre>
<pre>
1.In([1,2,3])
GetContext("a":1)
Returns: true
Returns: "a"


[1].In([[1],2,3])
["a":1, "b":2, 2:3].GetContext(_)
Returns: false
Returns: ["a", "b", 2]


DateTime(2017).In([DateTime(2016), DateTime(2017)])
([1,2,3]:(_*2)).GetContext(_)
Returns: true
Returns: [1, 2, 3]


Count(eventLog.Events.TimeStamp.DateTime(_.Year).In([DateTime(2012)]).Where(_==true))
["a":1, 2, 3].GetContext(_)
Returns: The number of events there are in the given event log that have time stamp for year 2012.
Returns: ["a", null, null]
</pre>
</pre>
|-
|-
||StringJoin
||GetValueOfContext
||
||
# Separator between joined parts (String)
# Context object
# Array of items to join (Array)
# Hierarchical array
||
||
Joins the given array of values (converted to strings) by using given separator into a single string. If given a hierarchical object, applies the function as described in at the level that is one level up from the leaf level. The depth of the result is one level less than the object that was given as parameter.
Returns the value of specified context object in given hierarchical array, i.e. the value behind the key. If the key if found multiple times, the first occurrence is returned. Returns ''_empty'' if the given key was not found.


Examples:
Examples:
<pre>
<pre>
StringJoin(", ", [1,null,"foo",DateTime(2017)])
GetValueOfContext("bar", ["foo":1, "bar":2])
Returns: 1, , foo, 01/01/2017 00:00:00
Returns: [2]
 
GetValueOfContext("bar", ["foo":1, "bar":2, "bar":"second"])
Returns: [2]
 
GetValueOfContext("test", ["foo":1, "bar":2])
Returns: _empty
</pre>
|-
||<span id="SendEmail">SendEmail</span>
||Parameters dictionary
||Sends an email message using the provided parameters. [[PA_Configuration_database_table#SMTP_Server_Settings|SMTP server settings]] need to be configured to be able to send email messages. Following parameters are supported:
* '''From''' (string) (mandatory): Email address where the email message appears to be coming from. Doesn't need to be a working address. If the From address is not defined, the system configured from address is used ([[PA Configuration database table#SMTP_Server_Settings|more information]]).
* '''To''' (string array) (mandatory): List of email addresses where to send the message to. Note that there should be only one email address per string in the array.
* '''Subject''' (string): Subject (title) of the email message.
* '''Body''' (string): Body (text contents) of the email message.
* '''Cc''' (string array): List of email addresses where to send the email as Cc.
* '''Bcc''' (string array): List of email addresses where to send the email as Bcc.
* '''IsBodyHtml''' (boolean): Defines whether the email message body is html (''true'') or plain text (''false''). By default false.
* '''ReplyTo''' (string array): Defines a list of reply-to addresses for the email message.
* '''BodyEncoding''' (string): Defines encoding for the message body. Supported encodings are ''ASCIIEncoding'', ''UTF7Encoding'', ''UTF8Encoding'' (default), ''UnicodeEncoding'' and ''UTF32Encoding''. More information: https://docs.microsoft.com/en-us/dotnet/api/system.text.encoding?redirectedfrom=MSDN&view=netcore-3.1#remarks.
* '''SubjectEncoding''' (string): Defines encoding for the message subject. Supported encodings are same as in the ''BodyEncoding'' property. Default value is ''UTF8Encoding''.
* '''Priority''' (string): Defines the email message priority. Available values are ''High'', ''Normal'' (default) and ''Low''. More information: https://docs.microsoft.com/en-us/dotnet/api/system.net.mail.mailpriority.
* '''DeliveryNotification''' (string): Defines the delivery notifications for the email message. Specified as a comma separated list of following possible values ''Delay'', ''Never'', ''None'', ''OnFailure'' and ''OnSuccess''. More information: https://docs.microsoft.com/en-us/dotnet/api/system.net.mail.deliverynotificationoptions.
* '''Sender''' (string): Defines the sender's email address. Usually the sender doesn't need to be defined. The sender is additional information for the email server.


StringJoin(", ", [[1,2], [3,4]])
Example:
Returns: ["1, 2", "3, 4"]
<pre>
SendEmail(#{
  "From": "example.from@address.com",
  "To": ["recipient.one@address.com", "recipient.two@address.com", "recipient.three@address.com"],
  "Subject": "Example E-mail",
  "Body": "QPR ProcessAnalyzer example script started running"
});
</pre>
</pre>
|}
|-
||WriteLog
||
message (Object)
||
When used in a script, writes the given text to the script log. The log entry is also made to the QPR ProcessAnalyzer log file. If the provided parameter is not a string, it's converted into string.
 
The return value of the function is the provided message parameter, allowing to flexibly add the WriteLog into expressions (see the examples).
 
Examples:
<pre>
WriteLog("Calculation executed.")
Writes to log: Calculation executed.


=== Set functions ===
Sum(WriteLog([1, 2, 3, 4]))
Returns: 10
Also writes an entry into the log showing the [1, 2, 3, 4] -array in the pretty printed fashion.


{| class="wikitable"
NumberRange(0, 4).(WriteLog("Iteration #" + (_ + 1)), _)
!'''Function'''
Returns: [0, 1, 2, 3, 4]
!'''Parameters'''
Also writes the following entries into log:
! '''Description'''
  Iteration #1
  Iteration #2
  Iteration #3
  Iteration #4
  Iteration #5
</pre>
|-
|-
||Distinct
||TakeSample
||
||
# Array or hierarchical object
* Input array/DataFrame
* Sample size (Integer)
* Seed (Integer)
* With replacements (Boolean)
||
||
Modify given array by filtering out all duplicate values leaving only distinct values in the input array into the result. If given a hierarchical object, applies the function at the level that is one level up from the leaf level.
Randomly chooses the defined number of items from the provided array or rows from the DataFrame, and returns a new array or DataFrame containing those items. If the sample size is larger than the number of items/rows, the TakeSample function has no effect. Note that the order of items may change from the original.
 
The seed is optional, and if not provided, the seed to be used is automatically generated.
 
The "with replacements" parameter is optional (by default false). When false, same items appear maximum of once in the resulting array. When true, same items may appear multiple times in the resulting array.


Examples:
Examples:
<pre>
<pre>
Distinct([1])
TakeSample([1,2,3,4,5,6,7,8,9,10], 4)
Returns: [1]
Returns [1, 9, 3, 4] (note: results may change)


Distinct([1, 1])
TakeSample([1,2,3,4,5,6,7,8,9,10], 5, 9, true)
Returns: [1]
Returns [5, 5, 1, 5, 3]


Distinct([1, 1, 2, 2])
TakeSample([1,2,3,4,5], 10)
Returns: [1, 2]
Returns [1,2,3,4,5]


Distinct([1, 1, 2, 2, "a", DateTime(2017), DateTime(2017), "b", DateTime(2016), "a"])
TakeSample(
Returns: [1, 2, "a", DateTime(2017), "b", DateTime(2016)]
  ToDataFrame(
    [[1,2],[3,4],[5,6]], ["a", "b"]
  ),
  2
).ToCsv()
Returns
a;b
5;6
3;4
</pre>
</pre>
|-
|-
||Intersect
||Eval
||Expression (String)
||
||
* First array
Evaluates the given expression. The Eval function can be used e.g., when the expression to be evaluated is generated during runtime, or is fetched during the script run from an external source. The expression is evaluated in the context where the Eval function exists, so it's possible to use variables defined outside the evaluated expression (see the example).
* Second array
 
* ...
Examples:
<pre>
Eval("1 + 1");
Result: 2
 
let myVar = 3;
Eval("myVar + 2");
Result: 5
</pre>
|-
||Sleep
||Duration (Timespan or integer as milliseconds)
||
||
Creates an intersection of multiple arrays. Returns a single array which contains an intersection of all the items in all the arrays given as parameter. If a parameter is not an array, it will be converted into an array having only the given element.
Suspends the expression execution for the given time duration. The parameter can either be a Timespan object or integer where the duration is specified as milliseconds.


Examples:
Examples:
<pre>
<pre>
Intersect([1, 2, "a", DateTime(2017), 5], ["a", 2, 3, DateTime(2017), 5], [DateTime(2017), "a", 2])
Sleep(Timespan(0, 0, 1)));
Returns: [2, "a", DateTime(2017)]
Result: Sleeps for 1 minute
 
Sleep(1000);
Sleep for 1 second
</pre>
</pre>
|-
|-
||Except
||ToSqlExpression
||
||Expression (String)
* First array
* Second array
* ...
||
||
 
Converts given string into SqlExpression. Note that the SqlExpression is not yet executed at this point.
Creates an substract given items from an array. Returns a single array which contains all the elements that were in the first array that were not in any of the subsequent arrays. If a parameter is not an array, it will be converted into an array having only the given element.


Examples:
Examples:
<pre>
<pre>
Except([1, 2, "a", DateTime(2017), 5, "b", 6], ["a", 2, 3, DateTime(2017), 5], [DateTime(2017), "a", 2, 6])
let expr = ToSqlExpression("Column(\"Cost\")");
Returns: [1, "b"]
</pre>
</pre>
|-
|-
||Union
||Query
||Query configuration (Object)
||
||
* First array
Runs expression query ([[Web_API:_Expression/query|Web API: Expression/query]]) and returns results as an in-memory DataFrame (for in-memory queries) or SqlDataFrame (for queries run in the datasource, e.g., in Snowflake). You can use charts to help with creating the configuration: Configure a chart, and click the ''Query'' button in the chart settings (''Advanced'' tab) to get the query configuration. Note that the json needs to be converted into expression language by adding hash characters (#) in the beginning of the curly braces of objects.
* Second array
 
* ...
Example: In-memory query:
||
<pre>
Creates an union of multiple arrays. Returns a single array which contains an union of all the items in all the arrays given as parameter. If a parameter is not an array, it will be converted into an array having only the given element.
let result = Query(#{
"ModelId": 123,
"Root": "Cases",
"MaximumRowCount": 10,
"Dimensions": [
#{ "Name": "Account manager", "Expression": "Attribute(\"Account Manager\")" }
],
"Values": [
#{ "Name": "Case count", "Expression": "Count(_)" }
],
"Ordering": [
#{ "Name": "Case count", "Direction": "Descending" }
]
});
</pre>


Examples:
Example: SqlDataFrame query ("Where" function is used additionally):
<pre>
<pre>
Union([1, 2, "a", DateTime(2017), 5], ["a", 2, 3, DateTime(2017), 5], [DateTime(2017), "a", 2, "b"])
let result = Query(#{
Returns: [1, 2, "a", DateTime(2017), 5, 3, "b"]
"ProcessingMethod": "dataframe",
"ContextType": "model",
"ModelId": 123,
"Root": "Cases",
"MaximumRowCount": 10,
"Dimensions": [
#{ "Name": "Account manager", "Expression": "Column(\"Account Manager\")" }
],
"Values": [
#{ "Name": "Case count", "AggregationFunction": "count" }
],
"Ordering": [
#{ "Name": "Case count", "Direction": "Descending" }
]
})
.Where(Like(Column("Account manager"), "Patricia%"))
.Collect();
</pre>
</pre>
|}
|}


=== Variable handling functions ===
== Ordering functions ==


{| class="wikitable"
{| class="wikitable"
!'''Function'''
!'''Function'''
!'''Parameters'''
! '''Parameters'''
! '''Description'''
! '''Description'''
|-
|-
||Def
||OrderBy (array)
||
||
# Function name (String)
# Array to order
# Variable 1 name (String)
# Order expression
# Variable 2 name (String)
# ...
# Function expression
||
||
Creates a new '''user defined function'''. Parameters starting from the second, are the parameters that user gives when calling the function. The last parameter is the expression to evaluate when the function is called. In that definition expression the named parameters are used. The created function is valid only within the current scope and all its child scopes.
Orders the given array using values from the given order expression. The order expression is evaluated once for each item in the array. The order expression supports all atomic (=not collection) primitive value types.


Examples:
Examples:
<pre>
<pre>
Def("Inc", "a", a + 1); Inc(2);
OrderBy(["a", "x", "b", "z", "n", "l", "a"], _)
Returns: 3
Return:
["a", "a", "b", "l", "n", "x", "z"]


Def("Add", "a", "b", a + b); [1, 2, 3].Add(_, 2);
OrderBy([9,8,7,6,5,4,3,2,1], _%3)
Returns: [3, 4, 5]
Returns:
[9,6,3,7,4,1,8,5,2]


Def("AddSelf", "b", _ + b); [1, 2, 3].AddSelf(2);
OrderBy([9,8,7,6,5,4,3,2,1], _%3 + _/30)
Returns: [3, 4, 5]
Returns:
 
[3,6,9,1,4,7,2,5,8]
Def("Fib", "a", If(a < 2, 1, Fib(a - 1) + Fib(a - 2))); Fib(10);
Returns: 89
</pre>
</pre>
|-
|-
||Let
||OrderByDescending (array)
||
||
# Variable 1 name (String)
# Array to order
# Variable 1 value (Object)
# Order expression
# Variable 2 name (String)
||Result is same as in the OrderBy function, except the order is reverse.
# Variable 2 value (Object)
|-
# ...
||OrderByTop (array)
# Expression to evaluate
||
# Array to order
# Order expression
||
||
Defines one or several '''user defined variables''' which can be used in the expression provided as the last parameter. Note that user defined variables cannot override existing properties.
Orders given top-level array by the value of given expression using ascending order. Supports all value types, including multi-dimensional arrays.
 
Order expression is evaluated in the context of each array item whose value determines the order of that item.


Examples:
Examples:
<pre>
<pre>
Let("var1", "Orange", "Value is " + var1)
OrderByTop([[1, 2, 3], [2, 2, 2], [3, 2, 1]], _[2])
Returns: Value is Orange
Returns: [[3, 2, 1], [2, 2, 2], [1, 2, 3]]
 
Let("var1", "Orange", "var2", "Mango", "Values are " + var1 + " and " + var2)
Returns: Values are Orange and Mango
</pre>
</pre>
|-
|-
||Set
||OrderByValue (array)
||
||
# Variable 1 name (String)
# Array to order
# Variable 1 value (Object)
# Variable 2 name (String)
# Variable 2 value (Object)
# ...
||
||
Set a value for a named variable. Can also be used to set several variable values at once. The variable to set must have been created in some visible scope by Let function. There can be any number of variable name and variable value pairs as long as the number of parameters is even number.
Orders the given array using the natural order of items, for example numbers it's the increasing order, and for strings it's text ordering.
<pre>
OrderByValue(["a", "x", "b", "z", "n", "l", "a"])
Return:
["a", "a", "b", "l", "n", "x", "z"]


If the number of parameters was two, the result is just the value that was set for that single value. If the number of parameters is more than two, the result is an array of all the set values.
</pre>
|-
|-
||Variable
||OrderByValueDescending (array)
||
||
# Variable name (String)
# Array to order
||
||
Function to get a variable value. The function is needed when there are spaces in a variable name, because that variable cannot be referenced otherwise in the expressions.
Result is same as in the OrderByValue function, except the order is reverse.
|}
|}


 
== Looping functions ==
=== Other functions ===
 
{| class="wikitable"
{| class="wikitable"
!'''Function'''
!'''Function'''
Line 607: Line 1,088:
! '''Description'''
! '''Description'''
|-
|-
||DateTime
||For
||
||
# Year (1-9999) (Integer)
# Iterated variable name (String)
# Month (1-12) (Integer)
# Initial value for iterated property (object)
# Day (>= 1) (Integer)
# Iteration condition expression
# Hour (>= 0) (Integer)
# Next iteration step expression
# Minute (>= 0) (Integer)
# Expression to calculate iterated items
# Second (>= 0) (Integer)
# Millisecond (>= 0) (Integer)
||
||
Creates a new [[QPR ProcessAnalyzer Expressions#DateTime|DateTime]] object. Only the first (year) parameter is mandatory.  
Iterates the given expression until the given condition is met, and returns the results of the iterated expressions for every iteration as an array.


Examples:
Examples:
<pre>
<pre>
DateTime(2017)
For("i", 0, i < 4, i + 1, i)
Returns: A datetime for 1st January 2017 at 0:00:00.
Returns: [0, 1, 2, 3]


DateTime(2017, 5)
For("x", 0, x < 3, x + 1, For("y", 0, y < 3, y + 1, StringJoin(",", [x, y]))
Returns: A datetime for 5th January 2017 at 0:00:00.
Returns: [["0,0", "0,1", "0,2"], ["1,0", "1,1", "1,2"], ["2,0", "2,1", "2,2"]]


DateTime(2017, 5, 6, 13, 34, 56, 123)
For("i", 0, i < 4, i + 1, DateTime(2010 + i))
Returns: A date time object for 6th May 2017 at 13:34:56.123.
Returns: [DateTime(2010), DateTime(2011), DateTime(2012), DateTime(2013)]
 
For("str", "a", str != "aaaaa", str + "a", str)
Returns: ["a", "aa", "aaa", "aaaa"]
</pre>
</pre>
|-
|-
||TimeSpan
||ForEach (2 parameters)
||
||
# Days (Integer)
# Array to iterate through
# Hours (Integer)
# Function to call
# Minutes (Integer)
# Seconds (Integer)
# Milliseconds (Integer)
||
||
Creates a new [[QPR ProcessAnalyzer Expressions#TimeSpan|Timespan]] object. Only the first parameter (number of days) is mandatory. By default, other parameters are assumed to be zero.
Calls given function using all the elements of given array, where the calculation is done in the current context. The function must support calling with one parameter. Returns the results of the evaluation of given function for every iteration of the input array as an array.


Examples:
Examples:
<pre>
<pre>
TimeSpan(1)
let sourceModel = First(Models.Where(Name == "SAP_OrderToCash"));
Returns: Time span for the duration of 1 day.
let attributes = sourceModel.EventAttributes;
let events = sourceModel.EventLog.Events;
let func = att => Attribute(att);
events.ForEach(attributes, func)
Returns an array containing arrays of all the event attribute values for all the events.
 
let myFunction = item => _ + item * 2;
(5).ForEach([1,2,3], myFunction)
Returns 7, 9, 11 (=5+1*2, 5+2*2, 5+3*2)
</pre>
|-
||ForEach (3 parameters)
||
# Variable to repeat (String)
# Array to repeat
# Expression to calculate repeated items
||
Repeats the given expression as many times there are items in the given array. Item in the array is available as the given variable in the expression.
Examples:
<pre>
ForEach("i", [1,2,3], "Value is: " + i)
Returns:
Value is: 1
Value is: 2
Value is: 3
 
ForEach("item", EventLogById(1).Cases, Let("c" + item.Name, item))
Results: Creates expression variable variables like "c<casename>" for every case in the model.


TimeSpan(12,3,4,5,6)
ForEach("myVariable", ["a", "b", "c"], myVariable + myVariable)
Returns: Time span for the duration of 12 days, 3 hours, 4 minutes, 5 seconds and 6 milliseconds.
Returns:
aa
bb
cc
</pre>
</pre>
|-
|-
||Catch
||NumberRange
||
||
# Expression to calculate
# Start (Number)
# Result if exception (Object)
# End (Number)
# Interval (Number)
||
||
Calculates the given expression and if any exceptions are thrown during the calculation, catches that exception and returns the given result. Note that this function does not catch any syntactical errors.
Creates an array of numbers within the given range using the given interval. Interval parameter is optional, and by default it is one. The interval can also be negative to get a sequence of decreasing numbers (then the Start needs to be greater than the End).


Examples:
Examples:
<pre>
<pre>
Catch(1, 1234)
NumberRange(1, 3)
Returns: 1
Returns: [1, 2, 3]
 
NumberRange(-7, -1, 2)
Returns: [-7, -5, -3, -1]
 
NumberRange(1, 3, 0.5)
Returns: [1, 1.5, 2, 2.5, 3]


Catch(undefined, 1234)
NumberRange(1, 3, 0.8)
Returns: 1234
Returns: [1, 1.8, 2.6]


Catch([1,2].undefined, 1234)
NumberRange(6, 2, -2)
Returns: 1234
Returns: [6, 4, 2]
</pre>
|-
||Repeat
||
# Number of times to repeat (Integer)
# Expression to repeat
||
Repeats the defined expression the defined number of times.
Examples:
<pre>
Repeat(3, "Repeat me!")
Returns:
"Repeat me!"
"Repeat me!"
"Repeat me!"


Catch(EventLogById(-1), 1234)
Repeat(1, 5)
Returns: 1234
Returns
5
</pre>
|-
||TimeRange
||
# Start (DateTime)
# End (DateTime)
# Interval (Timespan)
||
Generates a timestamp array starting from the start timestamp with the defined interval, and ending when the end timestamp is reached. Note that this function only creates timestamps with equal durations, so it's not possible to e.g. create timestamps for each month (to do that, you can use the loops).
<pre>
Generate datetimes starting from Monday 2017-01-01 and ending to Monday 2017-12-31 including all Mondays between them:
Timerange(Datetime(2018,1,1), Datetime(2018,1,1), Timespan(7))
</pre>
</pre>
|}
== Recursion functions ==
{| class="wikitable"
!'''Function'''
! '''Parameters'''
! '''Description'''
|-
|-
|-
||Coalesce
||Recurse
||
||
# Object to coalesce
# Expression to call recursively
# Result if Null
# Stop condition expression
# Maximum depth (Integer)
||
||
Returns the second parameter if the first parameter evaluates to null or empty. If given a hierarchical object, applies the function at the leaf level. If the the given object is a hierarchical object, all its leaf level values are coalesced separately.
Evaluates the given expression recursively until given condition or recursion depth is met. The function returns all the traversed objects in a single array. When the stop condition expression evaluates to false, it will stop the current recursion without including the false evaluated object into the result. Default stop condition is '''!IsNull(_)'''. The default maximum depth is 1000.


Examples:
Examples:
<pre>
<pre>
Coalesce(0, 1)
(1).Recurse(_ + 1, _ < 5)
Returns: 0
Returns: [1, 2, 3, 4]


Coalesce(null, 1)
event.Recurse(NextInCase)
Coalesce(_empty, 1)
Returns: An array of all the events following given event inside the same case.
Coalesce(_remove, 1)
All return: 1


Coalesce([[null, 1], [null, null]], 3)
event.Recurse(NextInCase, Type.Name != "Invoice")
Returns: [[3, 1], [3, 3]]
Returns: An array of all the events following given event inside the same case until event whose type name is "Invoice", which will itself not be included into the result.


Coalesce([[null, 1], 2], 3)
event.Recurse(NextInCase, Type.Name != "Invoice", 2)
Returns: [[3, 1], null]
Returns: An array of all the events following given event inside the same case until event whose type name is "Invoice" or until the recursion depth of 2 has been reached, which will itself not be included into the result.
 
Coalesce([1, [null, 2], null], 3)
Returns: [1, [null, 2], 3]
</pre>
</pre>
|-
|-
||EventLogById
||RecurseWithHierarchy
||
||
# FilterId (Integer)
# Expression to call recursively
# Stop condition expression
# Maximum depth (Integer)
||
||
Returns [[QPR_ProcessAnalyzer_Expressions:_Type_Specific_Properties_and_Functions#EventLog|EventLog]] object corresponding to the provided filter Id.
Evaluates the given expression recursively until given condition or recursion depth is met. The function returns the traversed object hierarchy. When the stop condition expression evaluates to false, it will stop the current recursion without including the false evaluated object into the result. Default stop condition is '''!IsNull(_)'''. The default maximum depth is 1000.
 
Examples:
<pre>
[1,2].RecurseWithHierarchy([1,2], false, 2)
Returns: [1:[1:[1,2],2:[1,2]],2:[1:[1,2], 2:[1,2]]]
 
(1).RecurseWithHierarchy(_ + 1, _ < 5)
Returns: 1:[2:[3:[4]]]
 
RemoveLeaves(eventLog.Flows:From.Where(IsNull(_))).To.RecurseWithHierarchy(OutgoingFlows.To, !IsNull(_), 2)
Returns: A hierarchy consisting of all the starter events of given event log and recursively all the event types reachable from them via flows until depth of 2 is reached.
</pre>
|-
|-
||ModelById
||RecursiveFind
||
||
# ModelId (Integer)
# Expression to call recursively
# Find condition expression
# Stop condition expression
# Continue after finding (Boolean)
# Maximum depth (Integer)
||
||
Returns [[QPR ProcessAnalyzer Expressions#Model|Model]] object corresponding to the provided model Id.
Evaluates given expression recursively until given condition or recursion depth is met. The function collects all the traversed objects that match the given find expression along the way. When the find condition expression evaluates to true for the current object, it causes the following:
* Current object is added to the result array returned by the function call
* If continue after finding is false, the recursion will not be continued on this branch
 
When the stop condition expression evaluates to false, it will stop the current recursion without including the false evaluated object into the result. Default stop condition is '''!IsNull(_)'''. The default maximum depth is 1000.
 
Continue after finding tells should the recursion be continued after a match has been found in the current branch.
 
Examples:
<pre>
(1).RecursiveFind(_ + 1, _ == 100)
Returns: 100
 
eventLog.Cases:GetAt(0, Events).RecursiveFind(NextInCase, Organization=="Finance", !IsNull(_))
Returns: For each case, returns the first (by time) event whose organization equals to "Finance".
 
eventLog.Cases:Events.Where(Organization=="Finance")
eventLog.Cases:GetAt(0, Events).RecursiveFind(NextInCase, Organization=="Finance", true, true)
Returns: Both return for each case all events whose organization equals to "Finance".
</pre>
|}
 
== Hierachical object functions ==
{| class="wikitable"
!'''Function'''
!'''Parameters'''
! '''Description'''
|-
|-
||FindRepeats
||FindRepeats
Line 781: Line 1,375:
Returns: ["a", 1, "b", 2]
Returns: ["a", 1, "b", 2]
</pre>
</pre>
|-
||If
||
# Condition expression
# True expression
# False expression
||
Evaluates the condition expression and if it's true, returns the value of the second parameter. Otherwise returns the value of the third parameter. Always evaluates only either the second or the third parameter, but never both.
Examples:
<pre>
If(Now.Second % 2 == 0, "Even second", "Odd second")
Returns:
"Event second" or "Odd second" depending on the time of the evaluation.
For("i", 0, i < 10, i + 1, i).If(_ % 2 == 0, _, _remove)
Returns:
[0, 2, 4, 6, 8]
NOTE: Is equivalent to: For("i", 0, i < 10, i + 1, i).Where(_ % 2 == 0)
</pre>
|-
||IsNull (Boolean)
||
# Value to test (Object)
||
Return true if the provided value is null, otherwise returns false.
|-
|-
||RemoveLeaves
||RemoveLeaves
Line 885: Line 1,453:
Returns: [3, 4, 5, 6]
Returns: [3, 4, 5, 6]
</pre>
</pre>
|}
== Snowflake connection functions ==
{| class="wikitable"
!'''Function'''
!'''Parameters'''
! '''Description'''
|-
|-
||Where
||<span id="CallStoredProcedure">CallStoredProcedure</span>
||
||
# Condition expression
# Procedure name (string)
# False expression
# Procedure parameters (dictionary)
# Additional parameters (dictionary)
||
||
Returns the context object if the given expression evaluates to true. Otherwise returns the second parameter. The second parameter is optional and it's by default EMPTY.
Calls a Snowflake stored procedure (it needs to pre-exist in the Snowflake when this function is called). The function returns the return value of the stored procedure. If the result is a table, its contents will be returned as an in-memory DataFrame. Object results are returned as JSON strings.


Examples:
Parameters:
# '''Procedure name''': Name of the called stored procedure.
# '''Procedure parameters''': Dictionary of parameters passed to the stored procedure. Note: If the parameter name starts with an underscore (_), it's not be passed to the procedure call.
# '''Additional parameters''': Dictionary of additional parameters affecting how the stored procedure is called. Supports parameter '''HasTabularResult''' (default ''false'') which need to be ''true'' when the stored procedure is expected to return a tabular result, and ''false'' when a scalar result. Note: When using the HasTabularResult=true, Snowflake ODBC driver version needs to be at least 3.1.0 (due to bug fix in the driver).
 
Security note: All QPR ProcessAnalyzer users can use the CallStoredProcedure function and call Snowflake stored procedures that are in the schema configured to the [[PA_Configuration_database_table#General_Settings|Snowflake connection string]]. Create only stored procedures to that schema, that are safe for any user to run.
 
Example 1: Stored procedure to calculate a sum of two numbers. Create following stored procedure to Snowflake:
<pre>
<pre>
[1,2,3,4].Where(_>2)
CREATE OR REPLACE PROCEDURE SUMOFNUMBERS(NUMBER1 NUMBER, NUMBER2 NUMBER)
Returns: [3,4]
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS OWNER
AS '
BEGIN
  RETURN NUMBER1 + NUMBER2;
END';
</pre>


[1,2,3,4].Where(_>2, _+100)
The stored procedure can be called as follows:
Returns: [101, 102, 3,4]
<pre>
CreateSnowflakeConnection()
  .CallStoredProcedure(
    "SUMOFNUMBERS",
    #{
      "NUMBER1": 3,
      "NUMBER2": 5
    }
  );


[1,2,3].[_,_+1]
Returns: 8
Returns: [[1, 2], [2, 3], [3, 4]]
</pre>


[1,2,3].[_,_+1].Where(_>=3)
Example 2: Calls a stored procedure named train_ml_model with a dictionary parameter containing InputEvents key that has SQL query string as value which, when run in the same Snowflake connection, will return all the contents of data table. ColumnMappings key that has a dict-object containing column role mappings of the InputEvents data.
Returns: [[], [3], [3, 4]]
<pre>
 
let dt = ModelById(1).EventsDataTable;
[1,2,3].[_,_+1].Where(_>=3, _remove)
CreateSnowflakeConnection()
Returns: [[3], [3, 4]]
  .CallStoredProcedure("train_ml_model", #{
    "ColumnMappings": dt.ColumnMappings,
    "InputEvents": dt.SqlDataFrame
  });
</pre>
</pre>
|-
|-
||GetValueOfContext
||<span id="CreateSnowflakeConnection">CreateSnowflakeConnection</span>
||Parameters dictionary
||
||
# Context object
Creates an object representing connection to Snowflake. Takes as a parameter a dictionary with following settings names:
# Hierarchical array from which the value is searched
* '''OdbcConnectionString''': Connection to Snowflake is made with this connection string.
||
* '''OdbcConnectionStringKey''': [[Storing_Secrets_for_Scripts|Secret name]] containing the ODBC connection string to connect to Snowflake.
Returns the value of specified context object in given hierarchical array. Returns the the first value of specified context object in the given hierarchical array. If a match is found, the result will be an array. Returns _empty if the given key was not found.
* '''ProjectId''': Project id to use as the context of the secure string. If not defined, project id is queried from the current context object. Project id needs to be available when using the ''OdbcConnectionStringKey'' setting.
 
If neither ''OdbcConnectionString'' or ''OdbcConnectionStringKey'' is defined, the system configured connection string will be used.


Examples:
Examples:
<pre>
<pre>
GetValueOfContext("bar", ["foo":1, "bar":2])
ProjectByName("My project")
Returns: [2]
  .CreateSnowflakeConnection(
    #{ "OdbcConnectionStringKey": "MyConnectionStringKey" }
  );
</pre>
|-
||ToSqlDataFrame
||In-memory dataframe
||Same functionality as in the [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#ToSqlDataFrame|Model.ToSqlDataFrame]], except the SQL dataframe is created for the connection which the function is called for.


GetValueOfContext("bar", ["foo":1, "bar":2, "bar":"second"])
Example: Create an SQL dataframe that is passed as parameter to a Snowflake procedure:
Returns: [2]
<pre>
 
let df = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);
GetValueOfContext("test", ["foo":1, "bar":2])
let connection = project.CreateSnowflakeConnection();
Returns: _empty
connection.CallStoredProcedure("MyProcedure", #{"input": connection.ToSqlDataFrame(df)});
</pre>
</pre>
|}
|}
[[Category: QPR UI]]

Revision as of 19:29, 29 April 2024

Aggregation functions

Following aggregation functions are available:

  • Average: Average of the items (sum of items divided by count of items). Can be used for numbers, DateTimes and Timespans.
  • Count: Number of items. Can be used for any type of data.
  • Median: Median, i.e. the middle value of the sorted data. If there are even number of items, the average of the two middle items. Can be used for numbers, DateTimes and Timespans.
  • Min: Lowest/first value in the data. Can be used for data that can be sorted.
  • Max: Highest/last value in the data. Can be used for data that can be sorted.
  • Percentile: Percentile value. Can be used for numbers, DateTimes and Timespans.
  • Stdev: Sample standard deviation. Can be calculated for numbers, DateTimes and Timespans.
  • Stdevp: Population standard deviation. Can be calculated for numbers, DateTimes and Timespans.
  • Sum: Sum of the items. Can be used for numbers and Timespans.
  • StringJoin: Joins array of strings together. The first parameter is a string that is placed between the joined strings.
  • Var: Sample variance. Can be used for numbers.
  • Varp: Population variance. Can be used for numbers.

Examples:

Sum([3, 2, 4])
Returns: 9

Count([[1, 2], [3, 4, 5]])
Returns: [2, 3]

Sum([[1, 2], [3, 4, 5]])
Returns: [3, 12]

Percentile([1,2,3,4,5], 0.75)
Return: 4

Sum([])
Returns: null

StringJoin(", ", ["one", "two", "three"])
Returns: one, two, three

When aggregating numerical values, null values are treated as zeros. Nulls can be removed with RemoveNulls function. In the Min and Max functions, nulls are ignored. Null values can be removed before aggregating (the following example shows how).

Average([1, 5, null])
Returns: 2

Average(RemoveNulls([1, 5, null])
Returns: 3

Aggregation operations are performed to the leaf level. i.e. the deepest level in the hierarchy. When aggregating, the leaf level arrays are replaced by the aggregated values, and thus the depth of the hierarchy decreases by one. In addition to the aggregation functions, functions that modify the contents of leaf arrays (OrderBy, Distinct, ...), the operation will be performed separately for every leaf array.

OrderByValue([[4, 3], [2, 1]])
Returns: [[3, 4], [1, 2]]

Mathematical functions

Function Parameters Description
Abs (Number) Number

Calculates the absolute value of a specified number. Examples:

Abs(4)
Returns: 4

Abs(-2.7)
Returns: 2.7

Abs(0)
Returns: 0
Acos (Number) Number

Calculates the angle whose cosine is the specified number. The parameter is a number representing a cosine, which must be between -1 and 1. Examples:

Acos(0.3584)
Returns: 1.204242852965772

Acos(-0.3584)
Returns: 1.9373498006240213
ArgMax (Object)
  1. Array or hierarchical object
  2. Value expression

Returns those objects in the array giving maximum values for the given expression when evaluated in the object's context. Examples:

ArgMax([5, 1, 6, 4, 3, 3, 6, 5, 4, 1, 1], _)
Returns: [6, 6]

ArgMax(EventLogById(1).Cases, Duration)
Returns: An array of cases having the longest duration.
ArgMin (Object)
  1. Array or hierarchical object
  2. Value expression

Returns those objects in the array giving minimum values for the given expression when evaluated in the object's context. Examples:

ArgMin([5, 1, 6, 4, 3, 3, 6, 5, 4, 1, 1], _)
Returns: [1, 1, 1]

ArgMin(EventLogById(1).Cases, Duration)
Returns: An array of cases having the shortest duration.
Asin (Number) Number

Calculates the angle whose sine is the specified number. The parameter is a number representing a sine, which must be between -1 and 1. Examples:

Asin(-0.3584)
Returns: 0.36655347382912462

Asin(+0.3584)
Returns: -0.36655347382912462
Atan (Number) Number

Calculates the angle whose tangent is the specified number. Examples:

Atan(1)
Returns: 0.78539816339744828

Atan(-1)
Returns: -0.78539816339744828
Ceiling (Integer)

Number

Returns the smallest integer that is greater than or equal to the specified number. Example:

Ceiling(1.3)
Returns: 2
Cos (Number) Number

Calculates the cosine of the specified angle measured in radians. Examples:

Cos(1)
Returns: 0.54030230586813977

Cos(-1)
Returns: 0.54030230586813977
Exp (Number) Number

Calculates the e raised to the specified power. Examples:

Exp(1)
Returns: 2.7182818284590451

Exp(-1)
Returns: 0.36787944117144233
Floor (Integer)

Number

Returns the largest integer that is less than or equal to the specified number. Example:

Floor(1.9)
Returns: 1
Hash (String) String to hash

Returns 11 characters long hash value for the given string. There can be following characters in the hash: A-Za-z0-9+/. For null or empty string, "AAAAAAAAAAA" is returned.

The hashing is a repeatable operation, i.e., the same input string always returns the same hash value. Note also that hashing lead to collisions, i.e., two different input strings may give the same hash value.

The hash is calculated using Murmur3 algorithm (https://blog.teamleadnet.com/2012/08/murmurhash3-ultra-fast-hash-algorithm.html) and the first 64 bits of the 128-bit hash value is returned encoded as base64url without padding (RFC 4648 §5).

Examples:

Hash("abcdef")
Returns: 3sqiubzVXxA
IEEERemainder (Number)
  1. Dividend (Number)
  2. Divisor (Number)

Calculates the remainder resulting from the division of a specified number by another specified number. Uses formula:

dividend - (divisor * Round(dividend / divisor))

Note that the formula is different than in the % operator, which uses formula:

(Abs(dividend) - (Abs(divisor) * (Floor(Math.Abs(dividend) / Abs(divisor))))) * Sign(dividend)  

Examples:

IEEERemainder(26, 4)
Returns: 2

IEEERemainder(0, 0)
Returns: NaN
Log (Number)
  1. Number
  2. Logarithm base (Number)

Calculates the logarithm of a specified number. If the second parameter is not provided, it's the natural logarithm (base e). The second parameter can be used to define another base for the logarithm. Examples:

Log(5)
Returns: 1.6094379124341003

Log(10, 2)
Returns: 3.3219280948873626
Log10 (Number)

Number

Calculates the base 10 logarithm of a specified number. Examples:

Log10(5)
Returns: 0.69897000433601886

Log10(-5)
Returns: NaN
Pow (Number)
  1. Base number
  2. Exponent number

Calculate a specified number raised to the specified power. Examples:

Pow(5, 3)
Returns: 125

Pow(-5, 3)
Returns: -125
Random (Number) (none) Returns a random number value between 0 and 1, where 0 is included, but 1 is not.

Example:

Random()
Returns: 0.1243234243 (for example)
Round (Float)

Number of decimals (Integer)

For numbers, rounds the number to the specified number of decimal. Number of decimals can also be negative, in which case the number is rounded to the nearest tens, hundreds, thousands, etc.

For DateTimes, rounds given date time by given time span or given number of seconds.

Note that the number and DateTime to be rounded needs to be provided as a context object (not as a parameter).

Examples:

(1.254).Round(1)
Returns: 1.3

(162.111111).Round(-2)
Returns: 200

DateTime(2017, 1, 2, 3, 4, 5).Round(10)
Returns: DateTime(2017, 1, 2, 3, 4, 10)

DateTime(2017, 1, 2, 3, 4, 5).Round(TimeSpan(1))
Returns: DateTime(2017, 1, 2)
Sign (Number)

Number

Calculates an integer that indicates the sign of a number, either -1, 0 or 1. Examples:

Sign(10)
Returns: 1

Sign(0)
Returns: 0

Sign(10)
Returns: -1
Sin (Number)

Number

Calculates the sine of the specified angle. Examples:

Sin(3.14)
Returns: 0.0015926529164868282

Sin(-3.14)
Returns: -0.0015926529164868282
Sqrt (Number)

Number

Calculates the square root of a specified number. Examples:

Sqrt(81)
Returns: 9

Sqrt(0.9)
Returns: 0.3
Tan (Number)

Number

Calculates the tangent of the specified angle. Examples:

Tan(1)
Returns: 1.5574077246549023

Tan(-1)
Returns: -1.5574077246549023

Conversion functions

Function Parameters Description
ToFloat (Number)

Object to convert

Converts the given object to a decimal number.
ToInteger (Integer)

Object to convert

Converts the given object into an integer. If the object can not be converted into an integer, an exception will be thrown. If the object as a decimal number, it will also be rounded to the nearest integer.

Examples:

ToInteger(1.234)
Returns: 1

ToInteger(-1.5)
Returns: -2

ToInteger(1313.6)
Returns: 1314

ToInteger(123456789012345678)
Returns: 123456789012345678

ToInteger("5")
Returns: 5

ToInteger("-5")
Returns: -5
ToString (String)
  1. Object to convert
  2. Date/Number/Timespan format (String)

Converts the given object to a string.

The optional second parameter defines formatting that is needed when converting the following data types:

Examples:

ToString(1) + " < " + ToString(2)
Returns: "1 < 2".

"Case=\'" + ToString(a) + "\'"
Returns: "Case='<string representation of object a>'"

ToString(DateTime(2017,1,2,3,4,5,6))
Returns: "2017-01-02T03:04:05"

ToString(DateTime(2017,1,2,3,4,5,6), "dd.MM.yyyy")
Returns "02.01.2017"

ToString(1.2, "0.00")
Returns "1.20"

ToString(TimeSpan(16,4,3,17,250), "%d")
Returns "16"

Data source connectors

Function Parameters Description
CallWebService (Dictionary) Dictionary of parameters

CallWebService is a generic function for any kind of http requests. Support natively REST-style web services by automatically formatting and parsing json in the request and response, so dictionary/array objects are used without converting them to strings (see examples below). If using xml-based web services, formatting and parsing of xml needs to be taken care of manually.

The function takes as a dictionary of following parameters:

  • Address (string): Url address to connect to.
  • Method (string): Http method to use, as one of the following: GET (default), POST, PUT, DELETE.
  • Body (string/dictionary/array): The http request body. In case the request content type is application/json, dictionary/array can be used directly without string conversion.
  • Content-Type (string): Defines the Content-Type http request header. If content type is application/x-www-form-urlencoded, form url encoding is applied for the request body.
  • Timeout (integer): Time to wait before the request times out, in case no response is received earlier. Defined as integer in milliseconds. By default 60000 milliseconds.
  • Headers (dictionary): Key-value pairs of HTTP request headers.
  • QueryParameters (dictionary): Key-value pairs of query parameters that are added to the url. Proper encoding of the url is taken care of by the function.
  • DefaultNetworkCredentials (boolean): When true, uses the default network credentials for the call. When false, the default network credentials are not used. The default network credentials can be used only if in the server configuration AllowForwardingNetworkCredentials setting is enabled.
  • Encoding (string): Defines the body encoding to use. Default value is UTF8. The supported options are listed in https://msdn.microsoft.com/en-us/library/system.text.encoding%28v=vs.110%29.aspx.

CallWebService returns a dictionary with properties:

  • Response (string/dictionary/array): Response data as string, or if content type is application/json, as dictionary/array object.
  • StatusCode (integer): Http status code.
  • Headers (dictionary): All http response headers.

The Content-Type http request header is set as follows: If the Content-Type parameter is provided, that is used. If Content-Type is not provided, and body is dictionary/array, request Content-Type is set to application/json. Otherwise no Content-Type header is set.

If the Content-Type parameter is application/x-www-form-urlencoded, body is expected to be a dictionary where the name/values pairs are encoded using FormUrlEncodedContent method (see examples below).

Examples:

Get a webpage and check the status code of the response:

let resp = CallWebService(#{
  "Address": "https://www.google.com/search?q=QPR+ProcessAnalyzer"
});
resp.StatusCode;

Get the server version in a QPR ProcessAnalyzer environment:

let resp = CallWebService(#{
  "Address": "https://processanalyzer.onqpr.com/qprpa/api/serverinfo"
});
resp.Response.serverVersion;

Authenticate to QPR ProcessAnalyzer (to get an access_token) and call expression query (note the Content-Type required by the token request):

let server = "http://processanalyzer.onqpr.com/qprpa";
let resp = CallWebService(#{
  "Address": server + "/token",
  "Method": "POST",
  "Content-Type": "application/x-www-form-urlencoded",
  "Body": #{
    "grant_type": "password",
    "username": "qpr",
    "password": "demo"
  }
});
resp = CallWebService(#{
  "Address": server + "/api/expression/query",
  "Method": "POST",
  "Content-Type": "application/json",
  "Headers": #{ "Authorization": "Bearer " + resp.Response.access_token },
  "Timeout": 120000,
  "Body": #{
    "Root": "Models",
    "Dimensions": null,
    "Values": [ #{"Name": "Name", "Expression": "Name"} ],
    "Ordering":[ #{"Name": "Name", "Direction": "Ascending"} ]
  }
});
resp.Response[0].Name

Upload contents of datatable id 321 to using Web API to a datatable id 123:

CallWebService(#{
  "Address": "https://processanalyzer.onqpr.com/qprpa/api/importfile?importMode=datatable&fileType=csv&objectId=123&append=true&csvTimeFormat=yyyy-MM-dd%20HH%3Amm%3Ass,fff",
  "Method": "POST",
  "Body": DatatableById(321).SqlDataFrame.Collect().ToCsv(),
  "Headers": #{ "Authorization": "Bearer " + resp.Response.access_token },
  "Timeout": 6000000
});
OpenAIChatCompletion (Dictionary) Request dictionary Calls OpenAI's Chat Completions API (https://platform.openai.com/docs/api-reference/chat) to generate natural language responses to user prompts. Both the request and response is a dictionary following the Chat Completions API specification. The messages parameter is the only mandatory fields in the request. The model parameter defines the OpenAI LLM model to use, and if not defined, model specified by the OpenAIDefaultModelName setting will be used.

To use the function, the OpenAi API key needs to be configured in OpenAIAPIKey configuration table.

Example: Response to a user request:

let response = OpenAIChatCompletion(#{
  "model":  "gpt-3.5-turbo",
  "messages": [
    #{
      "role": "system",
      "content": "You are a process mining expert making intelligent suggestions how to improve processes."
    },
    #{
      "role": "user",
      "content": "Give me three suggestions how to improve a sales process with bottlenecks."
    }
  ]
});
response["choices"][0]["message"]["content"];

Miscellaneous functions

Function Parameters Description
Where

Condition expression

Returns the context object if the given expression evaluates to true.

Examples:

[1,2,3,4].Where(_>2)
Returns: [3,4]

EventLogById(1).Cases.Where(Duration > TimeSpan(10))
Returns all cases where duration is more than 10 days (in eventlog id 1).

EventLogById(1).Events.Where(Timestamp < DateTime(2020, 1,1))
Returns all events which occurred before 1.1.2020 (in eventlog id 1).
If
  1. Condition expression
  2. True expression
  3. False expression

If the first parameter is true, returns the value of the second parameter. Otherwise returns the value of the third parameter. The function always evaluates only either the second or third parameter, but never both.

Note that the function needs to starts with a capital letter, because otherwise it's interpreted as the if operator.

Examples:

If(Now.Second % 2 == 0, "Even second", "Odd second")
Returns:
"Event second" or "Odd second" depending on the time of the evaluation.

For("i", 0, i < 10, i + 1, i).If(_ % 2 == 0, _, _remove)
Returns:
[0, 2, 4, 6, 8]
RemoveNulls (Array)

Array

Removes all the supported null values from given hierarchical object. Never removes arrays containing removed null values. Returns given object with null values removed.

Examples:

RemoveNulls([1, [], [null], [2, _remove, [_empty, 3, 4], _empty, null, 5]])
Returns: [1, [], [], [2, [3, 4], 5]]

RemoveNulls(["foo": null, "bar": 2, "poof": [1, _empty, 3]])
Returns: [
  "foo": 0,
  "bar": 2,
  "poof": [ 1, 3 ]
]
Catch
  1. Expression to calculate
  2. Result if exception (Object)

Calculates the given expression and if any exceptions are thrown during the calculation, catches that exception and returns the given result. Note that this function does not catch any syntactical errors.

Examples:

Catch(1, 1234)
Returns: 1

Catch(undefined, 1234)
Returns: 1234

Catch([1,2].undefined, 1234)
Returns: 1234

Catch(EventLogById(-1), 1234)
Returns: 1234
AsParallel

Additional parameters

Performs all the following chaining operations in parallel to improve performance. Items are divided into parts which size is determined by the ParallelTaskSize parameter, and each part is executed as an independent task in parallel. Parallel execution has a certain cost, so it might not be optimal to run each item as a separate task (increase ParallelTaskSize to decrease number of tasks). On the other hand, to large ParallelTaskSize leads to too few parallel tasks, and then all processing capacity is not used.

As a parameter, takes a dictionary that accepts optional property ParallelTaskSize, which is the size of the segments the root array will be split into. If the length of the input array is not divisible by ParallelTaskSize, the last segment will have less items than this configured value. The default size is 1.

The AsParallel function call doesn't need to be part of the chaining, and thus it can be called before the chaining.

Examples:

Run several ODBC queries simultaneously:
let regions = ["New York", "Los Angeles", "Dallas", "London"];
AsParallel(["ParallelTaskSize": 1]);
regions.ImportOdbc("...", "SELECT * FROM [Data] Where Region = '" + _ + "'")

Run a complex filtering operation for cases:
Cases.AsParallel(["ParallelTaskSize": 1000]).Where(...)

Sum(Sum(([NumberRange(1, 100)].AsParallel(["ParallelTaskSize": 1]).For("i", 0, i < 100000, i + 1, i))[0]))
Returns: 499995000000
The same expression without parallel processing:
Sum(Sum(NumberRange(1, 100).For("i", 0, i < 100000, i + 1, i)))
Returns: 499995000000

Sum([NumberRange(1, 100)].AsParallel(["ParallelTaskSize": 1]).WriteLog(_))
Returns: 5050
In addition, outputs all the numbers from 1 to 100 into log file in the order in which the tasks were executed.

The previous example can also be written like this:
AsParallel(["ParallelTaskSize": 1]);
Sum(NumberRange(1, 100).WriteLog(_));
Returns: 5050

Count((([el.Cases].AsParallel(["ParallelTaskSize": 1000]):AnalyzeConformance(cm))[0]).Where(_==null))
Returns the number of cases conforming to a design/conformance model cm.
The same expression without parallel processing:
Count((el.Cases:AnalyzeConformance(cm)).Where(_==null))

AsParallel(["ParallelTaskSize": 1]);
el.Cases.Events.Type.Name
Returns a hierarchical object having cases of given event log (el) as root objects and array of event type names of the events in each case in the order of occurrence.
The expressions related to every case in the returned hierarchical object are processed in a separate independent task (thus, each task will process "Events.Type.Name" expression in a separate task where the root object is a case).

[[123, 456, 789]].AsParallel(["ParallelTaskSize": 1]).ScriptById(_).Run()
Runs scripts with id's 123, 456 and 789 simultaneously.
IsNull (Boolean)

Value to test (Object)

Tests whether given object is null, _empty or _remove. Returns true if it is any of those. If given a hierarchical object, applies the function as described in at the leaf level.

Examples:

ForEach("item", [1, "a", null, _empty, _remove], IsNull(item))
Returns: [false, false, true, true, true]

IsNull(["foo":[null,"a"], "bar":[2,null]])
Returns: [
  HierarchicalArray("foo", [true, false]),
  HierarchicalArray("bar", [false, true])
]
IsNullTop (Boolean)

Object to test (Object)

Tests whether given object is null, _empty or _remove. Returns true if it is any of those. The function does not aggregate values in hierarchical objects.

Examples:

ForEach("item", [1, "a", null, _empty, _remove], IsNullTop(item))
Returns: [false, false, true, true, true]

IsNullTop(["foo":[null,"a"], "bar":[2,null]])
Returns: false
GroupBy
  1. Array to group
  2. Group by expressions

Groups given array by given expressions. Returns the given array splitted into groups in a way that each specified group expression creates one level of hierarchical arrays having the root object the same as the group expression result.

Examples:

GroupBy([1,2,2,3,3,4,5,5,4,4,4], _)

Returns:
[[1:[1], 2:[2, 2], 3:[3, 3], 4:[4, 4, 4, 4], 5:[5,5]]
GroupByValue Array to group

Groups all unique values in given array. Returns the given array in a format which has all the values in the original array only once as root objects and the root objects in the original array as contents of the arrays inside contexts.

In a way, this just switches root objects of hierarchical arrays to be the actual values and actual values to be root objects (without duplicates).

For every item in the array, the behavior is as follows:

  • If the item is not an array, the value is used also as the root object of the item.
  • If the item is an array, all the values are treated separately.
  • If the item is a hierarchical array, its root object will be used as root object and each separate value treated separately as scalars (ending up being root objects of the resulting object).

Examples:

GroupByValue([1,2,2,3,3,4,5,5,4,4,4])
Returns:
(The same as GroupBy([1,2,2,3,3,4,5,5,4,4,4], _))
[1:[1], 2:[2, 2], 3:[3, 3], 4:[4, 4, 4, 4], 5:[5,5]]

GroupByValue([1:["a","b"],2:["b","c"],3:["c"],4:["d","e"],5:["d","d","d"]])
Returns:
["a":[1], "b":[1, 2], "c":[2, 3], "d":[4, 5, 5, 5], "e":[4]]

Get count of item "2" in the array:
Let(\"groupped\", GroupByValue([1,2,4,1,4,2,3,3,2,4])); Count(GetValueOfContext(2, groupped))
Returns: 3
Coalesce
  1. Object to coalesce
  2. Result if Null

Returns the second parameter if the first parameter is null or empty. If the the given object is a hierarchical object, all the leaf level values are coalesced separately.

Examples:

Coalesce(0, 1)
Returns: 0

Coalesce(null, 1)
Coalesce(_empty, 1)
Coalesce(_remove, 1)
All return: 1

Coalesce([[null, 1], [null, null]], 3)
Returns: [[3, 1], [3, 3]]

Coalesce([[null, 1], 2], 3)
Returns: [[3, 1], null]

Coalesce([1, [null, 2], null], 3)
Returns: [1, [null, 2], 3]
Transpose

Matrix to transpose

Transposes the given matrix.

Examples:

Transpose([[1,2], [3,4], [1,4]])
Returns: [[1, 3, 1], [2, 4, 4]]
GarbageCollection Calling this function performs a garbage collection in the QPR ProcessAnalyzer Server. The function returns the maximum generation before the garbage collection was run. This function is an experimental functionality, which purpose is to investigate the memory consumption and memory handling behavior of the QPR ProcessAnalyzer Server.
GetContext

Hierarchical array

Returns the context of given hierarchical array, i.e. list of keys in the object. If the given object is not an hierarchical array, returns null.

Examples:

GetContext("a":1)
Returns: "a"

["a":1, "b":2, 2:3].GetContext(_)
Returns: ["a", "b", 2]

([1,2,3]:(_*2)).GetContext(_)
Returns: [1, 2, 3]

["a":1, 2, 3].GetContext(_)
Returns: ["a", null, null]
GetValueOfContext
  1. Context object
  2. Hierarchical array

Returns the value of specified context object in given hierarchical array, i.e. the value behind the key. If the key if found multiple times, the first occurrence is returned. Returns _empty if the given key was not found.

Examples:

GetValueOfContext("bar", ["foo":1, "bar":2])
Returns: [2]

GetValueOfContext("bar", ["foo":1, "bar":2, "bar":"second"])
Returns: [2]

GetValueOfContext("test", ["foo":1, "bar":2])
Returns: _empty
SendEmail Parameters dictionary Sends an email message using the provided parameters. SMTP server settings need to be configured to be able to send email messages. Following parameters are supported:
  • From (string) (mandatory): Email address where the email message appears to be coming from. Doesn't need to be a working address. If the From address is not defined, the system configured from address is used (more information).
  • To (string array) (mandatory): List of email addresses where to send the message to. Note that there should be only one email address per string in the array.
  • Subject (string): Subject (title) of the email message.
  • Body (string): Body (text contents) of the email message.
  • Cc (string array): List of email addresses where to send the email as Cc.
  • Bcc (string array): List of email addresses where to send the email as Bcc.
  • IsBodyHtml (boolean): Defines whether the email message body is html (true) or plain text (false). By default false.
  • ReplyTo (string array): Defines a list of reply-to addresses for the email message.
  • BodyEncoding (string): Defines encoding for the message body. Supported encodings are ASCIIEncoding, UTF7Encoding, UTF8Encoding (default), UnicodeEncoding and UTF32Encoding. More information: https://docs.microsoft.com/en-us/dotnet/api/system.text.encoding?redirectedfrom=MSDN&view=netcore-3.1#remarks.
  • SubjectEncoding (string): Defines encoding for the message subject. Supported encodings are same as in the BodyEncoding property. Default value is UTF8Encoding.
  • Priority (string): Defines the email message priority. Available values are High, Normal (default) and Low. More information: https://docs.microsoft.com/en-us/dotnet/api/system.net.mail.mailpriority.
  • DeliveryNotification (string): Defines the delivery notifications for the email message. Specified as a comma separated list of following possible values Delay, Never, None, OnFailure and OnSuccess. More information: https://docs.microsoft.com/en-us/dotnet/api/system.net.mail.deliverynotificationoptions.
  • Sender (string): Defines the sender's email address. Usually the sender doesn't need to be defined. The sender is additional information for the email server.

Example:

SendEmail(#{
  "From": "example.from@address.com",
  "To": ["recipient.one@address.com", "recipient.two@address.com", "recipient.three@address.com"],
  "Subject": "Example E-mail",
  "Body": "QPR ProcessAnalyzer example script started running"
});
WriteLog

message (Object)

When used in a script, writes the given text to the script log. The log entry is also made to the QPR ProcessAnalyzer log file. If the provided parameter is not a string, it's converted into string.

The return value of the function is the provided message parameter, allowing to flexibly add the WriteLog into expressions (see the examples).

Examples:

WriteLog("Calculation executed.")
Writes to log: Calculation executed.

Sum(WriteLog([1, 2, 3, 4]))
Returns: 10
Also writes an entry into the log showing the [1, 2, 3, 4] -array in the pretty printed fashion.

NumberRange(0, 4).(WriteLog("Iteration #" + (_ + 1)), _)
Returns: [0, 1, 2, 3, 4]
Also writes the following entries into log:
  Iteration #1
  Iteration #2
  Iteration #3
  Iteration #4
  Iteration #5
TakeSample
  • Input array/DataFrame
  • Sample size (Integer)
  • Seed (Integer)
  • With replacements (Boolean)

Randomly chooses the defined number of items from the provided array or rows from the DataFrame, and returns a new array or DataFrame containing those items. If the sample size is larger than the number of items/rows, the TakeSample function has no effect. Note that the order of items may change from the original.

The seed is optional, and if not provided, the seed to be used is automatically generated.

The "with replacements" parameter is optional (by default false). When false, same items appear maximum of once in the resulting array. When true, same items may appear multiple times in the resulting array.

Examples:

TakeSample([1,2,3,4,5,6,7,8,9,10], 4)
Returns [1, 9, 3, 4] (note: results may change)

TakeSample([1,2,3,4,5,6,7,8,9,10], 5, 9, true)
Returns [5, 5, 1, 5, 3]

TakeSample([1,2,3,4,5], 10)
Returns [1,2,3,4,5]

TakeSample(
  ToDataFrame(
    [[1,2],[3,4],[5,6]], ["a", "b"]
  ),
  2
).ToCsv()
Returns
a;b
5;6
3;4
Eval Expression (String)

Evaluates the given expression. The Eval function can be used e.g., when the expression to be evaluated is generated during runtime, or is fetched during the script run from an external source. The expression is evaluated in the context where the Eval function exists, so it's possible to use variables defined outside the evaluated expression (see the example).

Examples:

Eval("1 + 1");
Result: 2

let myVar = 3;
Eval("myVar + 2");
Result: 5
Sleep Duration (Timespan or integer as milliseconds)

Suspends the expression execution for the given time duration. The parameter can either be a Timespan object or integer where the duration is specified as milliseconds.

Examples:

Sleep(Timespan(0, 0, 1)));
Result: Sleeps for 1 minute

Sleep(1000);
Sleep for 1 second
ToSqlExpression Expression (String)

Converts given string into SqlExpression. Note that the SqlExpression is not yet executed at this point.

Examples:

let expr = ToSqlExpression("Column(\"Cost\")");
Query Query configuration (Object)

Runs expression query (Web API: Expression/query) and returns results as an in-memory DataFrame (for in-memory queries) or SqlDataFrame (for queries run in the datasource, e.g., in Snowflake). You can use charts to help with creating the configuration: Configure a chart, and click the Query button in the chart settings (Advanced tab) to get the query configuration. Note that the json needs to be converted into expression language by adding hash characters (#) in the beginning of the curly braces of objects.

Example: In-memory query:

let result = Query(#{
	"ModelId": 123,
	"Root": "Cases",
	"MaximumRowCount": 10,
	"Dimensions": [
		#{ "Name": "Account manager", "Expression": "Attribute(\"Account Manager\")" }
	],
	"Values": [
		#{ "Name": "Case count", "Expression": "Count(_)" }
	],
	"Ordering": [
		#{ "Name": "Case count", "Direction": "Descending" }
	]
});

Example: SqlDataFrame query ("Where" function is used additionally):

let result = Query(#{
	"ProcessingMethod": "dataframe",
	"ContextType": "model",
	"ModelId": 123,
	"Root": "Cases",
	"MaximumRowCount": 10,
	"Dimensions": [
		#{ "Name": "Account manager", "Expression": "Column(\"Account Manager\")" }
	],
	"Values": [
		#{ "Name": "Case count", "AggregationFunction": "count" }
	],
	"Ordering": [
		#{ "Name": "Case count", "Direction": "Descending" }
	]
})
.Where(Like(Column("Account manager"), "Patricia%"))
.Collect();

Ordering functions

Function Parameters Description
OrderBy (array)
  1. Array to order
  2. Order expression

Orders the given array using values from the given order expression. The order expression is evaluated once for each item in the array. The order expression supports all atomic (=not collection) primitive value types.

Examples:

OrderBy(["a", "x", "b", "z", "n", "l", "a"], _)
Return:
["a", "a", "b", "l", "n", "x", "z"]

OrderBy([9,8,7,6,5,4,3,2,1], _%3)
Returns:
[9,6,3,7,4,1,8,5,2]

OrderBy([9,8,7,6,5,4,3,2,1], _%3 + _/30)
Returns:
[3,6,9,1,4,7,2,5,8]
OrderByDescending (array)
  1. Array to order
  2. Order expression
Result is same as in the OrderBy function, except the order is reverse.
OrderByTop (array)
  1. Array to order
  2. Order expression

Orders given top-level array by the value of given expression using ascending order. Supports all value types, including multi-dimensional arrays.

Order expression is evaluated in the context of each array item whose value determines the order of that item.

Examples:

OrderByTop([[1, 2, 3], [2, 2, 2], [3, 2, 1]], _[2])
Returns: [[3, 2, 1], [2, 2, 2], [1, 2, 3]]
OrderByValue (array)
  1. Array to order

Orders the given array using the natural order of items, for example numbers it's the increasing order, and for strings it's text ordering.

OrderByValue(["a", "x", "b", "z", "n", "l", "a"])
Return:
["a", "a", "b", "l", "n", "x", "z"]

OrderByValueDescending (array)
  1. Array to order

Result is same as in the OrderByValue function, except the order is reverse.

Looping functions

Function Parameters Description
For
  1. Iterated variable name (String)
  2. Initial value for iterated property (object)
  3. Iteration condition expression
  4. Next iteration step expression
  5. Expression to calculate iterated items

Iterates the given expression until the given condition is met, and returns the results of the iterated expressions for every iteration as an array.

Examples:

For("i", 0, i < 4, i + 1, i)
Returns: [0, 1, 2, 3]

For("x", 0, x < 3, x + 1, For("y", 0, y < 3, y + 1, StringJoin(",", [x, y]))
Returns: [["0,0", "0,1", "0,2"], ["1,0", "1,1", "1,2"], ["2,0", "2,1", "2,2"]]

For("i", 0, i < 4, i + 1, DateTime(2010 + i))
Returns: [DateTime(2010), DateTime(2011), DateTime(2012), DateTime(2013)]

For("str", "a", str != "aaaaa", str + "a", str)
Returns: ["a", "aa", "aaa", "aaaa"]
ForEach (2 parameters)
  1. Array to iterate through
  2. Function to call

Calls given function using all the elements of given array, where the calculation is done in the current context. The function must support calling with one parameter. Returns the results of the evaluation of given function for every iteration of the input array as an array.

Examples:

let sourceModel = First(Models.Where(Name == "SAP_OrderToCash"));
let attributes = sourceModel.EventAttributes;
let events = sourceModel.EventLog.Events;
let func = att => Attribute(att);
events.ForEach(attributes, func)
Returns an array containing arrays of all the event attribute values for all the events.

let myFunction = item => _ + item * 2;
(5).ForEach([1,2,3], myFunction)
Returns 7, 9, 11 (=5+1*2, 5+2*2, 5+3*2)
ForEach (3 parameters)
  1. Variable to repeat (String)
  2. Array to repeat
  3. Expression to calculate repeated items

Repeats the given expression as many times there are items in the given array. Item in the array is available as the given variable in the expression. Examples:

ForEach("i", [1,2,3], "Value is: " + i)
Returns:
Value is: 1
Value is: 2
Value is: 3

ForEach("item", EventLogById(1).Cases, Let("c" + item.Name, item))
Results: Creates expression variable variables like "c<casename>" for every case in the model.

ForEach("myVariable", ["a", "b", "c"], myVariable + myVariable)
Returns:
aa
bb
cc
NumberRange
  1. Start (Number)
  2. End (Number)
  3. Interval (Number)

Creates an array of numbers within the given range using the given interval. Interval parameter is optional, and by default it is one. The interval can also be negative to get a sequence of decreasing numbers (then the Start needs to be greater than the End).

Examples:

NumberRange(1, 3)
Returns: [1, 2, 3]

NumberRange(-7, -1, 2)
Returns: [-7, -5, -3, -1]

NumberRange(1, 3, 0.5)
Returns: [1, 1.5, 2, 2.5, 3]

NumberRange(1, 3, 0.8)
Returns: [1, 1.8, 2.6]

NumberRange(6, 2, -2)
Returns: [6, 4, 2]
Repeat
  1. Number of times to repeat (Integer)
  2. Expression to repeat

Repeats the defined expression the defined number of times. Examples:

Repeat(3, "Repeat me!")
Returns:
"Repeat me!"
"Repeat me!"
"Repeat me!"

Repeat(1, 5)
Returns
5
TimeRange
  1. Start (DateTime)
  2. End (DateTime)
  3. Interval (Timespan)

Generates a timestamp array starting from the start timestamp with the defined interval, and ending when the end timestamp is reached. Note that this function only creates timestamps with equal durations, so it's not possible to e.g. create timestamps for each month (to do that, you can use the loops).

Generate datetimes starting from Monday 2017-01-01 and ending to Monday 2017-12-31 including all Mondays between them:
Timerange(Datetime(2018,1,1), Datetime(2018,1,1), Timespan(7))

Recursion functions

Function Parameters Description
Recurse
  1. Expression to call recursively
  2. Stop condition expression
  3. Maximum depth (Integer)

Evaluates the given expression recursively until given condition or recursion depth is met. The function returns all the traversed objects in a single array. When the stop condition expression evaluates to false, it will stop the current recursion without including the false evaluated object into the result. Default stop condition is !IsNull(_). The default maximum depth is 1000.

Examples:

(1).Recurse(_ + 1, _ < 5)
Returns: [1, 2, 3, 4]

event.Recurse(NextInCase)
Returns: An array of all the events following given event inside the same case.

event.Recurse(NextInCase, Type.Name != "Invoice")
Returns: An array of all the events following given event inside the same case until event whose type name is "Invoice", which will itself not be included into the result.

event.Recurse(NextInCase, Type.Name != "Invoice", 2)
Returns: An array of all the events following given event inside the same case until event whose type name is "Invoice" or until the recursion depth of 2 has been reached, which will itself not be included into the result.
RecurseWithHierarchy
  1. Expression to call recursively
  2. Stop condition expression
  3. Maximum depth (Integer)

Evaluates the given expression recursively until given condition or recursion depth is met. The function returns the traversed object hierarchy. When the stop condition expression evaluates to false, it will stop the current recursion without including the false evaluated object into the result. Default stop condition is !IsNull(_). The default maximum depth is 1000.

Examples:

[1,2].RecurseWithHierarchy([1,2], false, 2)
Returns: [1:[1:[1,2],2:[1,2]],2:[1:[1,2], 2:[1,2]]]

(1).RecurseWithHierarchy(_ + 1, _ < 5)
Returns: 1:[2:[3:[4]]]

RemoveLeaves(eventLog.Flows:From.Where(IsNull(_))).To.RecurseWithHierarchy(OutgoingFlows.To, !IsNull(_), 2)
Returns: A hierarchy consisting of all the starter events of given event log and recursively all the event types reachable from them via flows until depth of 2 is reached.
RecursiveFind
  1. Expression to call recursively
  2. Find condition expression
  3. Stop condition expression
  4. Continue after finding (Boolean)
  5. Maximum depth (Integer)

Evaluates given expression recursively until given condition or recursion depth is met. The function collects all the traversed objects that match the given find expression along the way. When the find condition expression evaluates to true for the current object, it causes the following:

  • Current object is added to the result array returned by the function call
  • If continue after finding is false, the recursion will not be continued on this branch

When the stop condition expression evaluates to false, it will stop the current recursion without including the false evaluated object into the result. Default stop condition is !IsNull(_). The default maximum depth is 1000.

Continue after finding tells should the recursion be continued after a match has been found in the current branch.

Examples:

(1).RecursiveFind(_ + 1, _ == 100)
Returns: 100

eventLog.Cases:GetAt(0, Events).RecursiveFind(NextInCase, Organization=="Finance", !IsNull(_))
Returns: For each case, returns the first (by time) event whose organization equals to "Finance".

eventLog.Cases:Events.Where(Organization=="Finance")
eventLog.Cases:GetAt(0, Events).RecursiveFind(NextInCase, Organization=="Finance", true, true)
Returns: Both return for each case all events whose organization equals to "Finance".

Hierachical object functions

Function Parameters Description
FindRepeats
  1. Array
  2. Minimum repeated length (Integer)
  3. Longest repeat length (Integer)

Searches for repeating patterns in given array. Parameters:

  1. Array to search the patterns in.
  2. Minimum integer length of a repeat that can be returned by this function (default = 0).
  3. Boolean value indicating whether to return all the repeating patterns (true) or only the ones having the longest length (false) (default = false)

Returns an array of arrays indicating the repeating patterns and their positions in the array formatted as follows:

  • Every repeating pattern has its own entry in the top-level array.
  • Every repeating pattern item in the top-level array is formatted internally as an array consisting of:
    • An array constining the repeating pattern itself.
  • An array consisting of all the indexes from which this pattern was found to start from.
  • Allows overlapping patterns.
  • Results are sorted primarily by the length of the pattern and secondarily by the number of indexes the pattern was found in.

Examples:

FindRepeats([0, 1, 2, 0, 1, 2])
Returns: [
  [[0, 1, 2], [0, 3]],
  [[0, 1], [0, 3]],
  [[1, 2], [1, 4]],
  [[0], [0, 3]],
  [[1], [1, 4]],
  [[2], [2, 5]]
]

FindRepeats([0, 1, 2, 0, 1, 2], 0, true)
Returns: [[[0, 1, 2], [0, 3]]]

FindRepeats([1, "b", DateTime(2017), 1, "b", DateTime(2017)], 3)
Returns: [[1, "b", DateTime(2017)], [0, 3]]
Flatten
  1. Array or hierarchical object
  2. Include context object (boolean)

Collects all the actual leaf values from given array, array of arrays or hierarchical object and returns them in a single array. If given a hierarchical object, this function collects actual leaf values instead of leaf level values. Elements in the returned array are in the same order they were found when traversing the input object using depth first search.

When context object is included, should context objects in the internal nodes of a hierarchical object be also included into the result. Default is false.

Examples:

Flatten(1)
Returns: 1

Flatten([1, 2])
Returns: [1,2]

Flatten([[[1, 2],[3, 4]],[[5, 6]]])
Returns: [1, 2, 3, 4, 5, 6]

Flatten([[1, 2], 3])
Returns: [1, 2, 3]

Flatten([[1,2,3,4], null, [5], [1, null]])
Returns: [1, 2, 3, 4, null, 5, 1, null]

Flatten(["a":1, "b":2])
Flatten(["a":1, "b":2], false)
Returns: [1, 2]

Flatten(["a":1, "b":2], true)
Returns: ["a", 1, "b", 2]
RemoveLeaves
  1. Hierarchical object

Remove one level of hierarchy from a hierarchical object replacing all the bottom level hierarchical arrays with the context objects of the hierarchical arrays. Returns the given object with all the bottom level hierarchical arrays with the context objects of the hierarchical arrays.

Examples:

RemoveLeaves(["foo":1, "bar":2])
Returns: ["foo", "bar"]

RemoveLeaves(eventLog.Flows:From.Where(IsNull(_)))
Results: All the flows starting the cases in the event log.
ReplaceLeafValues
  1. Array or hierarchical object
  2. Variable name used in the iteration (String)
  3. Expression to get the result of each iteration
  4. Number of levels up from the leaf level to operate (Integer)

Replace all leaf values of given array or hierarchical object at given levels up from the leaf level with results of given expression.

Examples:

ReplaceLeafValues([1,2, null], "x", If(IsNull(x), null, x+1), 0)
Result: [2, 4, null]

ReplaceLeafValues([[[1,2],[2,3]],[[3,4],[4,5]]], "x", Flatten(x), 0)
Result: [[[[1],[2]],[[2],[3]]],[[[3],[4]],[[4],[5]]]]

ReplaceLeafValues([[[1,2],[2,3]],[[3,4],[4,5]]], "x", Flatten(x), 1)
Result: [[[1,2],[2,3]],[[3,4],[4,5]]]

ReplaceLeafValues([[[1,2],[2,3]],[[3,4],[4,5]]], "x", Flatten(x), 2)
Result: [[1,2,2,3],[3,4,4,5]]

ReplaceLeafValues([[[1,2],[2,3]],[[3,4],[4,5]]], "x", Flatten(x), 3)
Result: [1,2,2,3,3,4,4,5]
SliceMiddle
  1. Start index for the range to extract (Integer)
  2. End index for the range to extract (Integer)
  3. Levels up in the hierarchy (Integer)
  4. Array to slice

Extracts a continuous range of an array or hierarchical object. If given a hierarchical object, applies the function at the level that is specified levels level up from the leaf level.

Start index: Negative value means that the index is counting from the end of the array. If array does not have element at this given index, empty array is returned.

End index: not included into the extracted range. Negative value means that the index is counting from the end of the array. If array does not have element at this given index, all the elements to the end from the start index will be extracted.

Levels up: At which level of the hierarchical object are we operating (number of levels up from the leaf level). Should be at least 1 (since 0 level does not contain arrays).

Examples:

SliceMiddle(1, 2, 1, [[0, 1, 2, 3], [4, 5, 6, 7]])
Returns: [[1], [5]]

SliceMiddle(2, 4, 1, [[0, 1, 2, 3], [4, 5, 6, 7]])
Returns: [[2, 3], [6, 7]]

SliceMiddle(0, 1, 2, [[0, 1, 2, 3], [4, 5, 6, 7]])
Returns: [0, 1, 2, 3]

SliceMiddle(3, 5, 1, [0, 1, 2, 3, 4, 5, 6, 7])
Returns: [3, 4]

SliceMiddle(-3, -1, 1, [0, 1, 2, 3, 4, 5, 6, 7])
Returns: [5, 6]

SliceMiddle(3, -1, 1, [0, 1, 2, 3, 4, 5, 6, 7])
Returns: [3, 4, 5, 6]

Snowflake connection functions

Function Parameters Description
CallStoredProcedure
  1. Procedure name (string)
  2. Procedure parameters (dictionary)
  3. Additional parameters (dictionary)

Calls a Snowflake stored procedure (it needs to pre-exist in the Snowflake when this function is called). The function returns the return value of the stored procedure. If the result is a table, its contents will be returned as an in-memory DataFrame. Object results are returned as JSON strings.

Parameters:

  1. Procedure name: Name of the called stored procedure.
  2. Procedure parameters: Dictionary of parameters passed to the stored procedure. Note: If the parameter name starts with an underscore (_), it's not be passed to the procedure call.
  3. Additional parameters: Dictionary of additional parameters affecting how the stored procedure is called. Supports parameter HasTabularResult (default false) which need to be true when the stored procedure is expected to return a tabular result, and false when a scalar result. Note: When using the HasTabularResult=true, Snowflake ODBC driver version needs to be at least 3.1.0 (due to bug fix in the driver).

Security note: All QPR ProcessAnalyzer users can use the CallStoredProcedure function and call Snowflake stored procedures that are in the schema configured to the Snowflake connection string. Create only stored procedures to that schema, that are safe for any user to run.

Example 1: Stored procedure to calculate a sum of two numbers. Create following stored procedure to Snowflake:

CREATE OR REPLACE PROCEDURE SUMOFNUMBERS(NUMBER1 NUMBER, NUMBER2 NUMBER)
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS OWNER
AS '
BEGIN
  RETURN NUMBER1 + NUMBER2;
END';

The stored procedure can be called as follows:

CreateSnowflakeConnection()
  .CallStoredProcedure(
    "SUMOFNUMBERS",
    #{
      "NUMBER1": 3,
      "NUMBER2": 5
    }
  );

Returns: 8

Example 2: Calls a stored procedure named train_ml_model with a dictionary parameter containing InputEvents key that has SQL query string as value which, when run in the same Snowflake connection, will return all the contents of data table. ColumnMappings key that has a dict-object containing column role mappings of the InputEvents data.

let dt = ModelById(1).EventsDataTable;
CreateSnowflakeConnection()
  .CallStoredProcedure("train_ml_model", #{
    "ColumnMappings": dt.ColumnMappings,
    "InputEvents": dt.SqlDataFrame
  });
CreateSnowflakeConnection Parameters dictionary

Creates an object representing connection to Snowflake. Takes as a parameter a dictionary with following settings names:

  • OdbcConnectionString: Connection to Snowflake is made with this connection string.
  • OdbcConnectionStringKey: Secret name containing the ODBC connection string to connect to Snowflake.
  • ProjectId: Project id to use as the context of the secure string. If not defined, project id is queried from the current context object. Project id needs to be available when using the OdbcConnectionStringKey setting.

If neither OdbcConnectionString or OdbcConnectionStringKey is defined, the system configured connection string will be used.

Examples:

ProjectByName("My project")
  .CreateSnowflakeConnection(
    #{ "OdbcConnectionStringKey": "MyConnectionStringKey" }
  );
ToSqlDataFrame In-memory dataframe Same functionality as in the Model.ToSqlDataFrame, except the SQL dataframe is created for the connection which the function is called for.

Example: Create an SQL dataframe that is passed as parameter to a Snowflake procedure:

let df = ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]);
let connection = project.CreateSnowflakeConnection();
connection.CallStoredProcedure("MyProcedure", #{"input": connection.ToSqlDataFrame(df)});