QPR ProcessAnalyzer Expressions: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
 
(245 intermediate revisions by the same user not shown)
Line 1: Line 1:
=== Expression and Evaluation Context ===
QPR ProcessAnalyzer expression language is a versatile query engine for all the QPR ProcessAnalyzer data and models. The expression language can perform variety of calculation operations, such as aggregations, dimensioning and following relations between objects.


An '''expression''' is a text to be evaluated that has a result. Result can be any of the supported object types or empty. An expression may consist of multiple expressions, called '''sub-expressions'''.
== Expression Language Overview ==
The picture below is an overview of all the major areas of the QPR ProcessAnalyzer expression language.


Expression evaluation is always performed within some '''context'''. This context and its type defines which kind of functionalities are available. '''Current context''' is '''implicitly''' accessible in all the expressions. Whenever a function or property is called, functions and properties accessible in the current context are searched first. If function or property is not found in the current context, then more generic context is tried. Error is returned only if the requested functionality is not available in the current context or a generic context. Current context can be accessed '''explicitly''' by using variable named '''_''' (underscore).
[[File:ExpressionLanguageOverview.png|850px]]


=== Expression Chaining and Hierarchies using . and : ===
The expression language has the following areas:
Expressions can be chained together two ways:
* '''Generic types''' cover the basic data structures familiar from many programming languages.
* '''Contextless chaining''': When '''.''' character is used to chain expressions, the resulting objects will not have context information.
* '''Configuration objects API''' allows to access the objects used to manage the content in QPR ProcessAnalyzer system. All objects are organized into projects which contains dashboards, models, datatables and scripts. In addition, models contain filters. Note that dashboards are not yet supported by the configuration objects API. There are also users and groups.
* '''Hierarchical chaining''': When ''':''' character is used to chain expressions, only the result of the whole chained expression will consist of hierarchical arrays (#29290#) where all the values in the first expression (=context object) will be bound to the arrays those values generated. If the second expression does not return an array, the result will be changed to be an array.
* '''Machine learning''' allow to perform machine learning libraries calculated by the QPR ProcessAnalyzer system.
* '''In-memory model API''' is the interface in the backend for the process mining calculations performed in the QPR ProcessAnalyzer Server memory. When the in-memory model API, the model needs to be loaded into the QPR ProcessAnalyzer Server memory and only after that the calculations can be done.
* '''DataFlow''' is used to read data from a datasource, transform the data and load it to a destination which are the basic steps of ETL. The core idea of the DataFlowis that the data is processed in small chunks, allowing to handle unlimited amount of data without large computing resources. Note that the DataFlow can only do transformations that can be done row by row, such as adding new column. For example, sorting the data is not possible, as it would require to load the entire dataset to be able to perform the sorting operation. The source of the data can be an external system where the extract the data or QPR ProcessAnalyzer's own datatables for further processing previously extracted data.
* '''DataFrame''' capsulates tabular datasets stored and processed in QPR ProcessAnalyzer Server memory. There is versatile set of functionality available to transform the data. Using the DataFrame requires that the entire dataset is loaded into the memory which sets certain limits how large datasets can be processed.
* '''SqlDataFrame''' is conceptually similar to the ''DataFrame'' except the data is not in the memory but it's located in the datasource where it's stored (either Snowflake or SQL Server). The transformations are in practice done using SQL that is processed by the datasource itself. Using the SqlDataFramedoesn't pose any requirements to the QPR ProcessAnalyzer Server itself as the data never ends to there. Instead Snowflake warehouse size or SQL Server capacity is an important factor how large datasets can be managed.
* '''SQL Expressions''' are part of the ''SqlDataFrame'' and they are expressions to make different kind of calculations to the data. Similar to the ''SqlDataFrame'', the SQL Expressions are converted into SQL for the actual calculation in the datasource.


The second expression chained to the first one will be evaluated using the following rules:
In the dashboards, there are two chart types available:
* If the result of the first expression is not an array, the second expression will be evaluated with the result of the first expression as its context object.
* '''Snowflake chart''' creates a '''Snowflake query''' to fetch the data. The in-memory query uses the ''SqlDataFrame'' to perform calculations.
* If the result of the first expression is an array, for every element in the array, the second expression will be evaluated with the array item as its context object. The result of the evaluation will be an array of evaluation results (one for each element in the array).
* '''In-memory chart''' creates an '''in-memory query''' to fetch the data. The in-memory query uses the ''In-memory model API'' to perform calculations. Thus, all the features and limitation of the In-memory model API affect the in-memory chart.
* If any of the second expression evaluations returns an array, the resulting object will be an array of arrays. If the first expression evaluation returns a typed array, the result will be hierarchic in a way that first level results are objects that contain the information about the actual object as well as the results generated by the second level expressions.


These rules apply also when chaining more than two expressions together. For example, it is possible to generate three level hierarchy with nodes of type: event log -> case -> event: '''EventLogById(1).Cases.Events or EventLogById(1):Cases:Events'''.
Expression language is run by QPR ProcessAnalyzer Server, and the expressions are used by dashboards and scripts.


Examples:
== Introduction to Expressions ==
<pre>
* '''Expression''' is sequence of calculation instructions written in a textual form, which are calculated by QPR ProcessAnalyzer Server.
Contextless chaining: First expression not an array, second expression not an array:
* Expressions may be broken down into '''sub-expressions''' which are calculated as part of the main expression calculation.
"1".("Number is " + _)
* Calculating an expression always gives a result which may be any type of object.
Returns:
* Expressions are always calculated within some '''context'''.
"Number is 1"
* Context can be accessed explicitly in an expression by using keyword '''_''' (underscore).
* Expression language is an object oriented, containing entities for all process mining objects (e.g. cases, events, variations) and also objects for managing content in QPR ProcessAnalyzer (models, projects, datatables).
* Each object has an own context in which object's '''functions''' and '''properties''' can be used.
* There is also the [[Generic_Objects_in_Expression_Language#Generic_Context|generic context]] which is used when the function or property is not found in the object specific context.
* If there is both a variable and a property with the same name in the same scope, the variable has the precedence, i.e., an identifier refers to the variable, not the property.


Contextless chaining: First expression is an array, second expression not an array:
== Chaining Expressions ==
[1,2,3].("Number is " + _)
Returns:
["Number is 1", "Number is 2", "Number is 3"]


Contextless chaining: First expression is an array, second expression is an array:
Two expressions can be chained together using the dot (.) operator. Depending on the types of the objects (whether they are arrays or scalar values) on the left and right side, the result varies, and it's summarized in the table below. Calculation functions as follows:
[1,2,3].["Number is " + _, "" + _ + ". number"]
* If the first expression result is a scalar (i.e. not an array), the second expression will be calculated with the first expression result as its context.
Returns:
* If the first expression result is an array, the second expression will be calculated for each left side array item as context. The result will be an array of calculation results.
[ ["Number is 1", "1. number"], ["Number is 2", "2. number"], ["Number is 3", "3. number"] ]
* If additionally the second expression calculations return arrays, the resulting object will be an array of arrays, i.e. a new array is created for each of the left side array items.
 
* If the left side expression is not an array, the calculation return a normal one-dimension array.
Hierarchical chaining: First expression is an array, second expression is an array:
[1,2,3]:["Number is " + _, "" + _ + ". number"]
Returns:
[ HierarchicalArray(1, ["Number is 1", "1. number"]), HierarchicalArray(2, ["Number is 2", "2. number"]), HierarchicalArray(3, ["Number is 3", "3. number"]) ]
 
</pre>
 
* '''Hierarchical arrays''': Whenever traversing a relation in expression language using hierarchical chaining operator ':' for chaining expressions, a hierarchical array will be returned. It is an object which behaves just like a normal array except it stores also context/root/key/label object which usually represents the object from which the array originated from, for example the original case object when querying events of a case.
* '''Hierarchical objects''': Arrays where at least one object in the array is itself an array is considered to be a '''hierarchical object'''. Hierarchical arrays are treated in similar way as normal arrays in hierarchical objects.
* '''Depth''' of a hierarchical object is the number of inner arrays that there are in the object, i.e. how deep is the hierarchy.
* '''Level''' in hierarchical object consists of all the nodes that are at specific depth in object's array hierarchy. '''0''' is the level at the root of the object, consisting only of the object itself as single item. Levels increase when moving towards leaves.
* '''Leaf level''' is a level that doesn't have any sub levels.
 
== Generic Properties and Functions ==
Generic Properties and Functions are available for all objects.
 
=== Generic Properties ===


{| class="wikitable"
{| class="wikitable"
!'''Property'''
! '''Left side'''
! '''Description'''
! '''Right side'''
! '''Result'''
|-
|-
||_
||scalar
||
||scalar
Refers to the current context.
||scalar
|-
||scalar
||array
||array
|-
|-
||_empty 
||array
||
||scalar
Returns an object which represent a non-existent value. Textual representation of this value is "EMPTY".
||array
|-
|-
||_remove 
||array
||
||array
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.
||two-dimensional array
|}


Examples:
Examples:
<pre>
<pre>
Both:
The left side is a string (i.e. not an array), so that string will be the context for the expression after the dot:
[1,2]._remove
"one".("Number is " + _.ToUpper())
[1,2].Where(_==3, _remove)
Result: Number is ONE
Return EMPTY
 
The left side is an array, so the right side expression will be calculated for each of the array items:
["one", "two", "three"].("Number is " + _.ToUpper())
Result (array of three strings): ["Number is ONE", "Number is TWO", "Number is THREE"]
 
Both the left and right sides are arrays, so the results is an array of arrays:
["one", "two", "three"].(["Number is " + _.ToUpper(), "First letter is " + _.Substring(0,1)])
Result (array of arrays containing strings):
[
["Number is ONE", "First letter is o"],
["Number is TWO", "First letter is t"],
["Number is THREE", "First letter is t"]
]


whereas
This is same as the previous, except an aggregation function is applied, which aggregates the deepest level:
StringJoin(", ", ["one", "two", "three"].(["Number is " + _.ToUpper(), "First letter is " + _.Substring(0,1)]))
Result: ["Number is ONE, First letter is o", "Number is TWO, First letter is t", "Number is THREE, First letter is t"]


[1,2].Where(_==3, _empty)
Sum([1, 2, 3].([_ + 4, _ + 5]))
[1,2].Where(_==3)
Result: [11, 13, 15]
[1,2]._empty
 
Returns an empty collection (collection of length 0).
Average(Sum([1, 2, 3].([_ + 4, _ + 5])))
Result: 13
 
let myCases = EventLogById(1).Cases
Average(myCases.Duration)
Result: Average duration of all cases in the EventLog 1.
 
let myCases = EventLogById(1).Cases
Average(
Count(
myCases.{
let lastEvent = LastEvent.Typename;
Events.Where(Typename==lastEvent)
}
)
)


For("i", 0, i < 10, i + 1, i).Where(_ % 2 == 0)
For("i", 0, i < 10, i + 1, i).If(_ % 2 == 0, _, _remove)
Both return:
[0, 2, 4, 6, 8]
</pre>
</pre>
|-
||CalcId (string)
||
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.
|-
||Models (Model*)
||All [[QPR ProcessAnalyzer Expressions#Model|Model]] objects in the QPR ProcessAnalyzer server (that the user have rights).
|-
||Now (DateTime)
||[[QPR ProcessAnalyzer Expressions#DateTime|DateTime]] object representing the current date and time.
|-
||Null 
||
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.
|-
||Projects (Project*)
||All Projects in the QPR ProcessAnalyzer server (that the user have rights).
|}


=== Aggregation Functions ===
== Operators ==
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'''.
Several expressions can be separated using semicolon (''';'''). Example:
<pre>
let var1 = 2;
let var2 = 3;
var1 + var2;
Returns: 5
</pre>


Examples:
Expression can contain line breaks without affecting the calculation.
<pre>
Count([[1, 2], [3, 4, 5]])
Returns: [2, 3]


Sum([[1, 2], [3, 4, 5]])
Following arithmetic and logical operations are available:
Returns: [3, 12]
* '''Addition (+)''' can be performed on numeric types, strings (concatenate two strings) and [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#TimeSpan|Timespans]] (addition of two TimeSpans).
* '''Subtraction (-)''' can be performed on numeric types, [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#DateTime|DateTimes]] (calculating TimeSpan between two DateTimes) and TimeSpans (difference between two TimeSpans).
* '''Multiplication (*)''' can be performed on numeric types, and between a TimeSpan and a numeric type (multiplying TimeSpan by number which results in TimeSpan),
* '''Division (/)''' can be performed on numeric types, between a TimeSpan and a numeric type (dividing a TimeSpan where TimeSpan must be the left hand side operand).
* '''Remainder (%)''': can be performed on numeric types.
* Comparison operators '''==''', '''<''', '''<=''', '''>''', '''>=''', '''!=''' are used to compare objects and return boolean values.
* Logical operators: '''&&''' (AND), '''||''' (OR), '''!''' (NOT) are used to combine expressions that return boolean values.


There is quick syntax for condition statement available:
<pre>
condition ? trueValue : falseValue
</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.
Example:
 
<pre>
<pre>
OrderByValue([[4, 3], [2, 1]])
var1 == 4 ? "Value is 4" : "Value is other than 4"
Returns: [[3, 4], [1, 2]]
</pre>
</pre>


=== Mathematical functions ===
== Common Datatypes ==
The expression language has the following basic datatypes available:


{| class="wikitable"
{| class="wikitable"
!'''Function'''
!'''Data type'''
!'''Parameters'''
!'''Expression language type'''
! '''Description'''
!'''Literal expression'''
! '''Use cases'''
! '''How to convert'''
|-
|-
||Ceiling (Integer)
||String
||String
||
||
# Number (Float)
<pre>
"Hello world!"
""
"Characters to escape: \" and \\"
"Line 1\nLine2"
</pre>
||Strings are textual values. The usual mistake is to store numbers and dates as strings which makes difficult to make calculations and comparison with them.
||To convert variable ''var1'' value to a string, use expression:
<pre>
let var1 = 295.01;
ToString(var1);
</pre>
|-
||Integer
||Int32 or Int64
||
||
Returns the smallest integer greater than or equal to the specified number.
<pre>
123456
-1234
0
</pre>
||Integers are whole numbers that describe numbers that don't have decimals, e.g. number of items etc. Equality comparisons with integers are possible.
||To convert variable ''var1'' value to an integer, use expression:
<pre>
let var1 = "29501";
ToInteger(var1);
</pre>
|-
|-
||Floor (Integer)
||Decimal&nbsp;number
||Double
||
||
# Number (Float)
<pre>
||
123.456
Returns the largest integer less than or equal to the specified number.
0.123
-12.34
1.5675E4
35E-6
</pre>
||Decimal numbers (expression language data type is double) may have decimals. Note that equality comparisons are not possible with decimal numbers.
||To convert variable ''var1'' value to a decimal number, use expression:
<pre>
let var1 = "295.01";
ToFloat(var1);
</pre>
|-
|-
||Round (Float)
||DateTime
||DateTime
||
||
# Number to be rounded (Float)
<pre>
# Number of decimals (Integer)
DateTime(2021, 5, 21, 15, 30, 13, 482)
||
</pre>
Rounds a value to the nearest integer or to the specified number of fractional digits.
||Dates are for the timestamps.
|}
||If data is as string, the string needs to be splited into different parts, convert them into integers and DateTime function used. Example:
 
<pre>
=== Ordering functions ===
let var1 = "2020-10-21T11:48:04.842";
 
let splitted=var1.Split(["-", "T", ":", "."]).ToInteger(_);
{| class="wikitable"
DateTime(splitted[0],splitted[1],splitted[2],splitted[3],splitted[4],splitted[5],splitted[6]);
!'''Function'''
</pre>
! '''Parameters'''
! '''Description'''
|-
|-
||OrderBy (array)
||Boolean
||
||Boolean
# Array to order
# 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:
<pre>
<pre>
OrderBy(["a", "x", "b", "z", "n", "l", "a"], _)
true
Return:
false
["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]
</pre>
</pre>
||Booleans can have either ''true'' or ''false''.
||If data is in a string, compare it agains a string literal "true". Example: <pre>var1=="true"</pre>
|-
|-
||OrderByDescending (array)
||Duration
||Timespan
||
||
# Array to order
(no literal presentation)
# Order expression
||Duration between two timestamps.
||Result is same as in the OrderBy function, except the order is reverse.
||Timespan() or TimespanFromTicks() functions.
|-
|-
||OrderByValue (array)
||Dictionary/JSON
||Dictionary/JSON
||
||
# 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.
<pre>
<pre>
OrderByValue(["a", "x", "b", "z", "n", "l", "a"])
#{"name": "John", "age": 30, "children": ["Anna", "Mia", "Eric"]}
Return:
#{"number": 1, "text": "Hello!", "datetime": DateTime(2020)}
["a", "a", "b", "l", "n", "x", "z"]
#{1: "number", "Hello!": "text", DateTime(2020): "datetime"}
 
</pre>
</pre>
|-
||OrderByValueDescending (array)
||
||
# Array to order
||
||
Result is same as in the OrderByValue function, except the order is reverse.
|}
|}


=== Recursion functions ===
Notes:
* Variables and datatable cells may also contain ''null'' values, which usually designate missing values etc.
* Expression language data type for an object is seen using '''InternalTypeName''' property


{| class="wikitable"
Notes on string literals:
!'''Function'''
*Characters '''"''' (double quote) and '''\''' (backslash) need to be escaped using the '''\''' (backslash) character.
! '''Parameters'''
* Linebreaks can be added with '''\n''' and tabulator with '''\t'''.
! '''Description'''
* Unicode characters can be added with '''\uXXXX''' where XXXX is a unicode character code, example: '''\u001f'''.
|-
 
For writing date and timespan value literals, use the [[Generic_Objects_in_Expression_Language#DateTime|DateTime]] and [[Generic_Objects_in_Expression_Language#Timespan|Timespan]] functions.


|-
== Arrays ==
||Recurse
'''Array''' is an object that is a list of any types of objects. Arrays can be created in an expression by enclosing a comma-separated list of items to brackets.
||
# Expression to call recursively
# 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.


Examples:
Examples:
<pre>
<pre>
(1).Recurse(_ + 1, _ < 5)
[1, 2, 3]
Returns: [1, 2, 3, 4]
Returns: An array with three elements: 1, 2 and 3.


event.Recurse(NextInCase)
["Dallas", "New York", "Los Angeles"]
Returns: An array of all the events following given event inside the same case.
Returns: Array of strings (region names).


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.
Returns: An empty array.
</pre>


event.Recurse(NextInCase, Type.Name != "Invoice", 2)
It's possible to apply operators (such as +, -, *, /, %, >, >=, <, <=, !=, ==) directly to arrays with the following rules:
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.
* If ''both operands are arrays'', the operator is applied separately for each item in the arrays in a way that items at the same index are applied with each other. If the lengths of arrays are different, an exception is thrown.
</pre>
* If ''only the left or right side operator is an array'', the operator is applied for each item in the array together with the non-array operand.
|-
* If ''both operands are not arrays'', the operator is applied directly to the objects.
||RecurseWithHierarchy
||
# Expression to call recursively
# Stop condition expression
# 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:
Examples:
<pre>
<pre>
[1,2].RecurseWithHierarchy([1,2], false, 2)
[1,2,3] + [4,5,6]
Returns: [1:[1:[1,2],2:[1,2]],2:[1:[1,2], 2:[1,2]]]
Returns: [5,7,9]


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


RemoveLeaves(eventLog.Flows:From.Where(IsNull(_))).To.RecurseWithHierarchy(OutgoingFlows.To, !IsNull(_), 2)
[1,2,null] ?? 3
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: [1,2,3]
 
[[1,2,3],[5,6,7]] + [1,2]
Returns: [[2,3,4],[7,8,9]]
</pre>
</pre>
|-
||RecursiveFind
||
# Expression to call recursively
# Find condition expression
# Stop condition expression
# Maximum depth (Integer)
# Continue after finding (Boolean)
||
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.
Arrays can also be used directly with those operators having one operand (-, !, ~). Examples:


Examples:
<pre>
<pre>
(1).RecursiveFind(_ + 1, _ == 100)
-[1,2,3]
Returns: 100
Returns: [-1,-2,-3]


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


eventLog.Cases:Events.Where(Organization=="Finance")
![0, [1, 0], 1]
eventLog.Cases:GetAt(0, Events).RecursiveFind(NextInCase, Organization=="Finance", true, true)
Returns: [true, [false, true], false]
Returns: Both return for each case all events whose organization equals to "Finance".
</pre>
</pre>
|}


=== Loop functions ===
Logical operators (&& and ||) don't support arrays in the way described previously. Also, the ''null coalescing'' operator (??) supports arrays only for left side operand, whereas the right side operand cannot be an array.
{| class="wikitable"
 
!'''Function'''
== Lookup operator ([ ]) ==
!'''Parameters'''
'''Lookup operator''' (brackets) is used to get one or several items from an array or a dictionary. Using the brackets, it's possible to define:
! '''Description'''
* single integer to get a single item from the array
|-
* array of integers to get multiple items from the array
||For
 
||
Note that the indices start from the zero, and using an index which is not in the array will throw an exception.
# Iterated variable name (String)
 
# Initial value for iterated property (object)
In addition to static values, it's possible to defined a lookup expression inside the brackets. The lookup expression is evaluated in the context of the array where items are to be fetched.
# 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.


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


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


For("i", 0, i < 4, i + 1, DateTime(2010 + i))
["a", "b", "c", "d"][[0, Count(_) - 1]]
Returns: [DateTime(2010), DateTime(2011), DateTime(2012), DateTime(2013)]
Returns: ["a", "d"]


For("str", "a", str != "aaaaa", str + "a", str)
[[1, 2], 3, [4, [5, 6]]][2][1][0]
Returns: ["a", "aa", "aaa", "aaaa"]
Returns: 5
</pre>
</pre>
|-
 
||ForEach
The lookup operator also works for dictionaries:
||
 
# 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:
Examples:
<pre>
<pre>
ForEach("i", [1,2,3], "Value is: " + i)
#{"a": 1, "b": 2, "c": 3}["b"]
Returns:
Returns: 2
Value is: 1
Value is: 2
Value is: 3


ForEach("myVariable", ["a", "b", "c"], myVariable + myVariable)
#{1: "number", "Hello!": "text", DateTime(2020): "datetime"}[[DateTime(2020), "Hello!", 1]]
Returns:
Returns: ["datetime", "text", "number"]
aa
bb
cc
</pre>
</pre>
|-
||NumberRange
||
# Start (Number)
# End (Number)
# Interval (Number)
||
Creates an array of numbers within given range with the given interval. Interval parameter is optional, and by default it is one.


Examples:
Also this kind of lookup can be used:
<pre>
<pre>
NumberRange(1, 3)
#{"a": 1, "b": 2, "c": 3}.b
Returns: [1, 2, 3]
Returns: 2
 
NumberRange(1, 3, 0.5)
Returns: [1, 1.5, 2, 2.5, 3]


NumberRange(1, 3, 0.8)
#{"a": 1, "b": 2, "c": 3}.(a+b+c)
Returns: [1, 1.8, 2.6]
Returns: 6
</pre>
</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!"


Repeat(1, 5)
== Define variables (let) and assign variable values (=) ==
Returns
Variables can be defined using the '''let''' operator:
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>
<pre>
Generate datetimes starting from Monday 2017-01-01 and ending to Monday 2017-12-31 including all Mondays between them:
let variableName = variableValue;
Timerange(Datetime(2018,1,1), Datetime(2018,1,1), Timespan(7))
</pre>
</pre>
|}
=== Array handling functions ===


{| class="wikitable"
Variables can only be used in the same scope where they are defined. Variable cannot be initialized, if there is already a variable with the same name in the same scope.
!'''Function'''
!'''Parameters'''
! '''Description'''
|-
||Array
||
# Item 1 (Object)
# Item 2 (Object)
# ...
||
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.


Examples:
Examples:
<pre>
<pre>
Array(1, 2, 3)
let myVariable1 = "myValue";
Returns: An array having three elements which are 1, 2 and 3.
let myVariable2 = Now;
Also following syntax can be used: [1, 2, 3]
let myVariable3 = 4;
</pre>


Array()
Variables can be defined without the initial value. In that case, the variables get and _empty value. Example:
Returns: An empty array.
<pre>
let myVariable1;
</pre>
</pre>
|-
||GetAt
||
# Index (Integer)
# Array
||
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.


Examples:
It's possible to assign (set) values to variables using the following syntax:
<pre>
<pre>
GetAt(0, [1,2,3])
variableName = variableValue;
Returns: 1
 
GetAt(1, [[1, 2], [3, 4]])
Returns: [3, 4]
 
GetAt(1, [[[5, 6]], [[1, 2], [3, 4]]])
Returns: [[1, 2], [3, 4]]
 
GetAt(1, GetAt(1, [[[5, 6]], [[1, 2], [3, 4]]]))
Returns: [3, 4]
</pre>
</pre>
|-
||GetAtReverse
||
# Index (Integer)
# Array
||
Same as the GetAt function, except that the index is calculated from the end of the array.
|-
||StringJoin
||
# Separator between joined parts (String)
# Array of items to join (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.


Examples:
Examples:
<pre>
<pre>
StringJoin(", ", [1,null,"foo",DateTime(2017)])
myVariable1 = "new value";
Returns: 1, , foo, 01/01/2017 00:00:00
myVariable2 = Now;
 
myVariable3 = myVariable3 + 1;
StringJoin(", ", [[1,2], [3,4]])
Returns: ["1, 2", "3, 4"]
</pre>
</pre>
|}


=== Set functions ===
Note that the variables need to be defined first to be able to set values for them.


{| class="wikitable"
== Conditional statement (if) ==
!'''Function'''
Conditions can be written using the "if" statement which has the following syntax:
!'''Parameters'''
<pre>
! '''Description'''
if (condition) {
|-
  //run if condition is true
||Intersect
} else {
||
  //run if condition is false
* First array
}
* Second array
</pre>
* ...
||
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.


Examples:
The "else" block is not mandatory:
<pre>
<pre>
Intersect([1, 2, "a", DateTime(2017), 5], ["a", 2, 3, DateTime(2017), 5], [DateTime(2017), "a", 2])
if (condition) {
Returns: [2, "a", DateTime(2017)]
  //run if condition is true
}
</pre>
</pre>
|-
||Except
||
* First array
* Second array
* ...
||


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.
It's also possible to chain if's:
<pre>
if (condition1) {
  //run if condition1 is true
} else if (condition2) {
  //run if condition2 is true (and condition1 false)
} else if (condition3) {
  //run if condition3 is true (and condition1 and condition2 false)
} else {
  //run if all of the above conditions are false
}
</pre>


Examples:
== Conditional statement (switch) ==
Conditions can be written using the "switch" statement, following the JavaScript syntax (https://www.w3schools.com/js/js_switch.asp). Switch statement is more limited than the "if" statement, but the switch is easier to read and performs faster than the "if" statement (in cases when it can be used). Also the "break" and "default" statements are usually used with the switch. Switch statement has the following syntax (where expr, x and y are expressions):
<pre>
<pre>
Except([1, 2, "a", DateTime(2017), 5, "b", 6], ["a", 2, 3, DateTime(2017), 5], [DateTime(2017), "a", 2, 6])
switch (expr) {
Returns: [1, "b"]
  case x:
    // code block
    break;
  case y:
    // code block
    break;
  default:
    // code block
}
</pre>
</pre>
|-
||Union
||
* First array
* Second array
* ...
||
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.


Examples:
Example: dimension expression where a case attribute value is mapped to a textual presentation.
<pre>
<pre>
Union([1, 2, "a", DateTime(2017), 5], ["a", 2, 3, DateTime(2017), 5], [DateTime(2017), "a", 2, "b"])
let label;
Returns: [1, 2, "a", DateTime(2017), 5, 3, "b"]
switch (Attribute("Shopping Cart Type")) {
  case "Standard":
    label = "Shopping cart type is standard";
    break;
  case "Fast track":
    label = "Shopping cart type is fast track";
    break;
  case null:
    label = "Shopping cart type is not defined";
    break;
  default:
    label = "Shopping cart type is other";
}
</pre>
</pre>
|}


=== Other functions ===
== Looping statement (for) ==
The ''for'' loop can be defined using the following syntax:
<pre>
for (initialization; condition; iterator) {
  statements;
}
</pre>


{| class="wikitable"
The ''initialization'' is evaluated in the beginning, and the ''iterator'' is evaluated after each iteration. The loop continues to the next iteration if the ''condition'' is true.
!'''Function'''
!'''Parameters'''
! '''Description'''
|-
||DateTime
||
# Year (Integer)
# Month (1-12) (Integer)
# Day (>= 1) (Integer)
# Hour (>= 0) (Integer)
# Minute (>= 0) (Integer)
# Second (>= 0) (Integer)
# Millisecond (>= 0) (Integer)
||
Creates a new [[QPR ProcessAnalyzer Expressions#DateTime|DateTime]] object. Only the first (year) parameter is mandatory.  


Examples:
Example (returns 10):
<pre>
<pre>
DateTime(2017)
let sum = 0;
Returns: A datetime for 1st January 2017 at 0:00:00.
for (let i = 1; i < 5; i++) {
  sum = sum + i;
}
return sum;
</pre>


DateTime(2017, 5)
== Looping statement (while) ==
Returns: A datetime for 5th January 2017 at 0:00:00.
The ''while'' loop can be defined using the following syntax:
<pre>
while (condition) {
  //looped as many times the condition is true
}
</pre>


DateTime(2017, 5, 6, 13, 34, 56, 123)
Example: loop is stopped when the ''counter'' variable reaches 5:
Returns: A date time object for 6th May 2017 at 13:34:56.123.
<pre>
let counter = 0;
while (counter < 5) {
  counter = counter + 1;
}
counter;
</pre>
</pre>
|-
||TimeSpan
||
# Days (Integer)
# Hours (Integer)
# 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.


Examples:
In addition, '''break''' (exit the while loop) and '''continue''' (start the next iteration) statements can be used with the while loop.
 
This example using ''break'' prints "12":
<pre>
<pre>
TimeSpan(1)
let items = "";
Returns: Time span for the duration of 1 day.
let i = 0;
 
while (i < 5) {
TimeSpan(12,3,4,5,6)
  i = i + 1;
Returns: Time span for the duration of 12 days, 3 hours, 4 minutes, 5 seconds and 6 milliseconds.
  if (i == 3) {
    break;
  }
  items = items + i;
}
items;
</pre>
</pre>
|-
||Catch
||
# Expression to calculate
# 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:
This example using ''continue'' prints "1245":
<pre>
<pre>
Catch(1, 1234)
let items = "";
Returns: 1
let i = 0;
while (i < 5) {
  i = i + 1;
  if (i == 3) {
    continue;
  }
  items = items + i;
}
items;
</pre>


Catch(undefined, 1234)
== Return statement ==
Returns: 1234
It's possible to return from the current block with a value using the '''return''' statement. Example:


Catch([1,2].undefined, 1234)
<pre>
Returns: 1234
return "value to return";
 
Catch(EventLogById(-1), 1234)
Returns: 1234
</pre>
</pre>
|-
||Coalesce
||
# Object to coalesce
# Result if Null
||
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.


Examples:
The return value is optional. If not given, the _empty value is returned. Example:
<pre>
<pre>
Coalesce(0, 1)
return;
Returns: 0
</pre>


Coalesce(null, 1)
The return statement works as follows in the different circumstances:
Coalesce(_empty, 1)
* If currently evaluating an user defined function, then the return value is returned to the caller of the user defined function as function call result.
Coalesce(_remove, 1)
* If currently evaluating a chained expression, the return value is used as the result of the chained evaluation.
All return: 1
* If currently evaluating an argument for a function call, then the return value is used as the value of the parameter being evaluated.
* Otherwise returns the value as the result of the evaluation of the whole expression.


Coalesce([[null, 1], [null, null]], 3)
== Null conditional operator (?.) ==
Returns: [[3, 1], [3, 3]]
The '''null conditional operator''' (one question mark) is useful in chaining operations where there might appear null values in the middle of the chain. If not handled correctly, e.g. trying to get a property from a null value, will throw an exception. In the null conditional operator, if the result of the left-hand side expression is a null value, the next step in the chaining operation is not executed, but a null value is returned instead.


Coalesce([[null, 1], 2], 3)
For example, the following expression throws an exception if ''StartTime'' is null:
Returns: [[3, 1], null]
<pre>
 
StartTime.Truncate("month")
Coalesce([1, [null, 2], null], 3)
Returns: [1, [null, 2], 3]
</pre>
</pre>
|-
||Def
||
# Function name (String)
# Variable 1 name (String)
# 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.


Examples:
The null conditional operator can be used to take into account the null situation, and the following expression returns null if ''StartTime'' is null:
<pre>
<pre>
Def("Inc", "a", a + 1); Inc(2);
StartTime?.Truncate("month")
Returns: 3
</pre>


Def("Add", "a", "b", a + b); [1, 2, 3].Add(_, 2);
In the null conditional operator, if the left-side expression is an array or a hierarchical array, the chaining operator does not chain null values in that array (see the examples). Null conditional chaining can be applied to both contextless and hierarchical chaining operations by prefixing the chaining operator with ? character.
Returns: [3, 4, 5]


Def("AddSelf", "b", _ + b); [1, 2, 3].AddSelf(2);
The null conditional operator is faster to calculate and syntax is easier to read than using [[Generic_Functions_in_QPR_ProcessAnalyzer#if|if]] condition.
Returns: [3, 4, 5]


Def("Fib", "a", If(a < 2, 1, Fib(a - 1) + Fib(a - 2))); Fib(10);
Examples:
Returns: 89
<pre>
[DateTime(2020, 3, 15), null]?.truncate("year")
Returns: [DateTime(2020), null]
(would return error without the null conditional operator)
</pre>
</pre>
|-
 
||Distinct
There is also a '''null conditional lookup''' operator which can be applied to the [[#Lookup operator|lookup operation]] by adding ? character in the front of the lookup operator. If used, and there is a null value instead of an array, the result of the lookup operation is null (an exception would be thrown without the null conditional lookup).
||
# Array or hierarchical object
||
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.


Examples:
Examples:
<pre>
<pre>
Distinct([1])
null?[3]
Returns: [1]
Returns: null


Distinct([1, 1])
[[1, 2], null, [3]].(_?[0])
Returns: [1]
Returns: [1, null, 3]
</pre>


Distinct([1, 1, 2, 2])
== Null coalescing operator (??) ==
Returns: [1, 2]
The '''null coalescing operator''' (two question marks) can be used to replace null values with something else. The null coalescing operator works as follows:
* If the left-hand side expression is null, the right-hand side value is returned.
* If the left-hand side expression is not null, the left-hand side value is returned.


Distinct([1, 1, 2, 2, "a", DateTime(2017), DateTime(2017), "b", DateTime(2016), "a"])
The null coalescing operator combined with the [[Generic_Objects_in_Expression_Language#_remove|_remove]] operator is one way to remove null values from an array (see the examples below), but the recommended way is to use the [[Generic_Functions_in_QPR_ProcessAnalyzer#RemoveNulls|RemoveNulls]] function.
Returns: [1, 2, "a", DateTime(2017), "b", DateTime(2016)]
</pre>
|-
||EventLogById
||
# FilterId (Integer)
||
Returns [[QPR ProcessAnalyzer Expressions#EventLog|EventLog]] object corresponding to the provided filter Id.
|-
||ModelById
||
# ModelId (Integer)
||
Returns [[QPR ProcessAnalyzer Expressions#Model|Model]] object corresponding to the provided model Id.
|-
||FindRepeats
||
||
|-
||Flatten
||
# Array or hierarchical object
||
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.


Examples:
Examples:
<pre>
<pre>
Flatten(1)
null ?? "foo"
Returns: "foo"
 
1 ?? "foo"
Returns: 1
Returns: 1


Flatten([1, 2])
[1, null, 3].(_ ?? "foo")
Returns: [1,2]
Returns: [1, "foo", 3]


Flatten([[[1, 2],[3, 4]],[[5, 6]]])
[1, null, 3].(_ ?? _remove)
Returns: [1, 2, 3, 4, 5, 6]
Returns: [1, 3]


Flatten([[1, 2], 3])
[1, null, 3]:(_ ?? _remove)
Returns: [1, 2, 3]
Returns: [1: [1], 3: [3]]
</pre>


Flatten([[1,2,3,4], null, [5], [1, null]])
== Increment and decrement operators ==
Returns: [1, 2, 3, 4, null, 5, 1, null]
Increment (++) and decrement (--) operators serve as a short syntax to increase/decrease numeric value by one. The operators can be used before (prefix) or after (postfix) the incremented/decremented variable. When used as prefix, the changed variable value is returned by the increment/decrement statement (i.e., it can be seen that the increment/decrement is made before the other operations in the same row are made). When used as postfix, the original variable value is returned by the increment/decrement statement (i.e., it can be seen that the increment/decrement is made after the other operations in the same row are made).
</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:
Examples:
<pre>
<pre>
If(Now.Second % 2 == 0, "Even second", "Odd second")
let myVar1 = 1;
Returns:
let myVar2 = myVar1++; // myVar2 = 1
"Event second" or "Odd second" depending on the time of the evaluation.
</pre>
 
<pre>
For("i", 0, i < 10, i + 1, i).If(_ % 2 == 0, _, _remove)
let myVar1 = 1;
Returns:
let myVar2 = ++myVar1; // myVar2 = 2
[0, 2, 4, 6, 8]
</pre>
NOTE: Is equivalent to: For("i", 0, i < 10, i + 1, i).Where(_ % 2 == 0)
<pre>
let myVar1 = 1;
let myVar2 = myVar1--; // myVar2 = 1
</pre>
<pre>
let myVar1 = 1;
let myVar2 = --myVar1; // myVar2 = 0
</pre>
</pre>
|-
||IsNull (Boolean)
||
# Value to test (Object)
||
Return true if the provided value is null, otherwise returns false.
|-
||Let
||
# Variable 1 name (String)
# Variable 1 value (Object)
# Variable 2 name (String)
# Variable 2 value (Object)
# ...
# Expression to evaluate
||
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.
Examples:
<pre>
<pre>
Let("var1", "Orange", "Value is " + var1)
let counter = 0;
Returns: Value is Orange
while (counter++ < 3) {
 
WriteLog(counter); // Writes to log: 1, 2, 3
Let("var1", "Orange", "var2", "Mango", "Values are " + var1 + " and " + var2)
}
Returns: Values are Orange and Mango
</pre>
</pre>
|-
||RemoveLeaves
||
# Hierarchical object
||
Removes leaves from a hierarchical object.
|-
||ReplaceLeafValues
||
# Array or hierarchical object
# Variable name used in the iteration (String)
# Expression to get the result of each iteration
# 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:
<pre>
<pre>
ReplaceLeafValues([1,2, null], "x", If(IsNull(x), null, x+1), 0)
let counter = 0;
Result: [2, 4, null]
while (++counter < 3) {
WriteLog(counter); // Writes to log: 1, 2
}
</pre>


ReplaceLeafValues([[[1,2],[2,3]],[[3,4],[4,5]]], "x", Flatten(x), 0)
== Catching exceptions ==
Result: [[[[1],[2]],[[2],[3]]],[[[3],[4]],[[4],[5]]]]
The try...catch statement is used to catch thrown exceptions, to handle the exception situation and continue executing script despite the exception. The statement comprises of a ''try'' block and either a ''catch'' block, a ''finally'' block, or both. The code in the ''try'' block is executed first, and if it throws an exception, the code in the ''catch'' block is executed. The code in the ''finally'' block will always be executed before control flow exits the entire construct.


ReplaceLeafValues([[[1,2],[2,3]],[[3,4],[4,5]]], "x", Flatten(x), 1)
try...catch statement structure:
Result: [[[1,2],[2,3]],[[3,4],[4,5]]]
<pre>
try {
  tryStatements
} catch (exceptionVariable) {
  catchStatements
} finally {
  finallyStatements
}
</pre>
There are following blocks in the above example:
* tryStatements: Statements to be executed.
* catchStatements: Statements that are executed if an exception is thrown in the try block.
* exceptionVariable: Optional identifier to hold the caught exception for the associated catch block.
* finallyStatements: Statements that are executed before control flow exits the construct. These statements execute regardless of whether an exception was thrown or caught.


ReplaceLeafValues([[[1,2],[2,3]],[[3,4],[4,5]]], "x", Flatten(x), 2)
Example to catch errors in data extraction, log the error and continue script execution:
Result: [[1,2,2,3],[3,4,4,5]]
<pre>
 
try {
ReplaceLeafValues([[[1,2],[2,3]],[[3,4],[4,5]]], "x", Flatten(x), 3)
  results = ExtractSap(queryParameters);
Result: [1,2,2,3,3,4,4,5]
} catch (error) {
  Writelog("Data extraction from SAP failed.");
}
</pre>
</pre>
|-
||Set
||
# Variable 1 name (String)
# 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.


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.
== Throwing objects ==
|-
Objects can be thrown using the '''throw''' statement. For example, strings can be thrown, but the thrown object can also be a more complex dictionary containing additional data. Note that there is no separate exception entity that can be thrown.
||SliceMiddle
||
# Start index for the range to extract (Integer)
# End index for the range to extract (Integer)
# Levels up in the hierarchy (Integer)
# 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.
Following example throws a string that can contain a message why the processing was stopped:
<pre>
throw "Error occurred etc.";
</pre>


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.
The thrown object can be caught using the '''try-catch''' statement. The caught entity is an exception object that has the thrown object in parameter '''thrownObject'''.


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).
Example:
 
Examples:
<pre>
<pre>
SliceMiddle(1, 2, 1, [[0, 1, 2, 3], [4, 5, 6, 7]])
try {
Returns: [[1], [5]]
  throw "Error occurred etc.";
 
} catch (exception) {
SliceMiddle(2, 4, 1, [[0, 1, 2, 3], [4, 5, 6, 7]])
  WriteLog(exception.thrownObject);
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]
</pre>
</pre>
|-
||Variable
||
# Variable name (String)
||
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.
|-
||Where
||
# Condition expression
# False expression
||
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.


Examples:
== Defining functions ==
Functions can be defined using following syntax (called lambda syntax):
<pre>
<pre>
[1,2,3,4].Where(_>2)
(param1, param2, param3) => (function definition)
Returns: [3,4]
 
[1,2,3,4].Where(_>2, _+100)
Returns: [101, 102, 3,4]
 
[1,2,3].[_,_+1]
Returns: [[1, 2], [2, 3], [3, 4]]
 
[1,2,3].[_,_+1].Where(_>=3)
Returns: [[], [3], [3, 4]]
 
[1,2,3].[_,_+1].Where(_>=3, _remove)
Returns: [[3], [3, 4]]
</pre>
</pre>
|-
||GetValueOfContext
||
# Context object
# Hierarchical array from which the value is searched
||
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.


Examples:
Examples:
<pre>
<pre>
GetValueOfContext("bar", ["foo":1, "bar":2])
Function to add to numbers:
Returns: [2]
(a, b) => a + b
 
GetValueOfContext("bar", ["foo":1, "bar":2, "bar":"second"])
Returns: [2]


GetValueOfContext("test", ["foo":1, "bar":2])
Function to return the current time:
Returns: _empty
() => Now
</pre>
</pre>
|}


== Type Specific Properties and Functions ==
'''FunctionDefinition''' encapsulates a function consisting of following parts:
* function body (expression)
* function parameters (optional)
* function name.


This chapter lists all the object types in the expresssion language and properties and functions that they support. After the property or function name there is the type of the returned object mentioned. Asterisk (*) after the type means that it returns an array of objects.
FunctionDefinition objects can be created in the following ways:
* Using function <function name>(<function arguments>)<function body> syntax
* Using (<function arguments>) => <function body> syntax
* Using Def function
* In Def function, use &-prefix is for an attribute


=== Array ===
Functions defined by a FunctionDefinition object can be called as follows: '''<function name>(<function arguments>)'''. Functions are evaluated in a new scope, i.e. variables defined in the function are only available within that function.


{| class="wikitable"
!'''Array functions'''
!'''Parameters'''
! '''Description'''
|-
||IndexOfSubArray (integer)
||
# Sub-array to search(array)
# Array from where to search the sub-array (array)
||Returns the indexes of given sub-array (1. parameter) within the given array (2. parameter). If not given, the array in the current context object is used. Returns starting indexes of all the occurrences of given sub-array within given array.
Examples:
Examples:
<pre>
<pre>
[[1,2,3,4,1,2,5]].IndexOfSubArray([1,2])
Def(null, "a", "b", a+b)._(1,2)
IndexOfSubArray([1,2], [1,2,3,4,1,2,5])
((a, b)=>a+b)._(1,2)
Return: [0, 4]
((a, b)=>a+b)(1,2)
Returns: 3


[[1,2,3,4,1,2,5]].IndexOfSubArray([1,2,3,4,5])
[Def("", "a", "b", a+b), Def("", "a", "b", a*b)].(_(1,2))
Returns: []
[(a, b)=>a+b, (a, b)=>a*b].(_(1,2))
Returns: [3, 2]


[[1,2,3,4,1,2,5],[3,4],[0,1,2,3]]:IndexOfSubArray([1,2])
Def("FirstOrNull", "arr", CountTop(arr) > 0 ? arr[0] : null)
Returns:
function FirstOrNull(arr) { CountTop(arr) > 0 ? arr[0] : null }
[
Result: Are all equivalent and create a function FirstOrNull into the current evaluation scope. Returns also the created FunctionDefinition object.
  HierarchicalArray([1,2,3,4,1,2,5], [0,4]),
  HierarchicalArray([0,1,2,3], [1])
]
</pre>
</pre>
|}


=== AttributeType ===
The FunctionDefinition objects have the following properties:
* '''Arguments''': Returns an array containing names of all arguments of the function.
* '''Body''': Expression body of the function as string.
* '''Name''': Name of the function if this is a named function.


{| class="wikitable"
It's possible to define functions that call themselves, which is used in recursive algorithms. There is a certain limit how many times a function can call other functions. Usually this limit may be reached with functions calling themselves. If this stack overflow situation is encountered, there may be a bug in the algorithm or then there are too many recursions for the system to handle.
!'''Case properties'''
! '''Description'''
|-
||Id (Integer)
||AttributeType Id.
|-
||Name (String)
||Attribute name.
|}


=== Case ===
== Template strings ==


{| class="wikitable"
'''Template strings''' are string literals enclosed with backticks (`) that can contain ''embedded expressions'' defined using syntax '''${expression}'''. The embedded expressions are evaluated in the same calculation context in which the template string is defined.
!'''Case properties'''
! '''Description'''
|-
||Duration (TimeSpan)
||Case duration, i.e. duration between case start and case end time.
|-
||EndTime (DateTime)
||Case end time, i.e. timestamp of the last event.
|-
||Events (Event)
||All events of the case.
|-
||FlowOccurrences (FlowOccurrence)
||All flow occurrences the case contains.
|-
||Flows (Flow)
||All flows the case goes through.
|-
||Id (String)
||Case Id. Case id is generated by QPR ProcessAnalyzer when the model is loaded.
|-
||Name (String)
||Case name. The case name comes with the source data when data is imported to QPR ProcessAnalyzer.
|-
||StartTime (DateTime)
||Case start time, i.e. timestamp of the first event.
|-
||Variation (Variation)
||Variation the case belongs to.
|}


{| class="wikitable"
Notes on template strings:
!'''Case&nbsp;functions'''
* If there is a need to use backticks in the template strings, escaping is done with a backslash (\`).
! '''Parameters'''
* Template strings can contain line breaks.
! '''Description'''
* Embedded expressions can also contain template strings.
|-
|| Attribute (Object)
||
* attribute name (string)
||Returns case attribute value. Case attribute name is provided as a parameter. This function is needed when there are spaces in the case attribute name. If there are no spaces, syntax '''.attributeName''' can be used.
|-
|| EventsByType (Event*)
||
* EventType object or EventType name (string)
||
(Available in QPR ProcessAnalyzer 2018.1) Returns all Events of this case which are of given type. The parameter can be either EventType object or the name of the event as a string.


Examples:
Examples:
<pre>
<pre>
case.EventsByType("Invoice")
let creditBlocksCount = 15;
Returns: Array of events having event type named "Invoice".
`There are total of ${creditBlocksCount} credit blocks!`;
Returns: There are total of 15 credit blocks!


case.EventsByType((EventLog.EventTypes.Where(Name=="Invoice"))[0])
EventLogById(1).Cases.`Case ${Name} contains ${Count(Events)} events`
Returns: Array of events having event type named "Invoice".
Returns a list of following strings for each case, e.g. Case 12345 contains 12 events
</pre>
|-
|| FlowOccurrencesByType (FlowOccurrence*)
||
* Flow object or id of FlowOccurrence(integer)
||
(Available in QPR ProcessAnalyzer 2018.1) Returns all FlowOccurrences of this case which are of given type. The parameter can be either a Flow object or an integer identifying the id of the flow occurrence.


Examples:
`${1+3}`
<pre>
Returns: 4
case.FlowOccurrencesByType(EventLog.Flows[0])
Returns: All the flow occurrences in the case belonging to the first flow in event log.


case.FlowOccurrencesByType(EventLog.Flows[0].Id)
`${`${1+1}`}`
Returns: All the flow occurrences in the case belonging to the first flow in event log.
Returns: 2
</pre>
|}


=== DateTime ===
Models.`Id:${Id}, Name: ${Name}`
DateTime represents a timestamp.
Returns: An array of strings containing model ids and names.


{| class="wikitable"
let m = [
!'''DateTime properties'''
  #{"Name":"Model 1", "Id": "1", "Description": "Big one"},
! '''Description'''
  #{"Name":"Model 2", "Id": "2", "Description": "This too"},
|-
  #{"Name":"Foo Model", "Id": "3", "Description": "Not this"}
||Day
];
||The day of the calendar month represented by the DateTime. Number between 1 and 31.
`<ul>${StringJoin("""", OrderBy(m, Name).`
|-
  <li>
||Hour
    Model: ${Id == 2 ? `(default) ${Name}`: Name}
||The hour component of the date represented by the DateTime. Number between 0 and 23.
    Id: ${Id}
|-
    Description: ${Description}
||Millisecond
  </li>`)}
||The millisecond component of the date represented by the DateTime. Number between 0 and 999.
</ul>`
|-
||Minute
||The minute component of the date represented by the DateTime. Number between 0 and 59.
|-
||Month
||The calendar month component of the date represented by the DateTime. Number between 1 and 12.
|-
||Second
||The second component of the date represented by the DateTime. Number between 0 and 59.
|-
||Year
||The year component of the date represented by the DateTime. Number between 1 and 9999.
|}


{| class="wikitable"
Returns:
!'''DateTime functions'''
<ul>
! '''Parameters'''
  <li>
! '''Description'''
    Model: Foo Model
|-
    Id: 3
|| Round (DateTime)
    Description: Not this
||
  </li>
* attribute name (TimeSpan)
  <li>
||
    Model: Model 1
Rounds the DateTime to the defined TimeSpan.
    Id: 1
    Description: Big one
  </li>
  <li>
    Model: (default) Model 2
    Id: 2
    Description: This too
  </li>
</ul>
</pre>


Example:
== Code comments ==
Single line comments can be added using // syntax. Line comment spans until the end of the line.
<pre>
<pre>
Round to the nearest hour:
let var1 = 123; //This is comment which is ignored by the calculation
Round(DateTime(2017, 1, 1, 14, 48), TimeSpan(0, 1))
</pre>
</pre>
|}


=== Event ===
Multiline comments can be added with syntax starting with /* and ending to */.
<pre>
/*
Comment that spans to
multiple lines.
*/
</pre>


{| class="wikitable"
== Expression Chaining using : keyword ==
!'''Event properties'''
! '''Description'''
|-
||Case (Case)
||Case the event belongs to.
|-
||Id (Integer)
||Event id. It's generated by QPR ProcessAnalyzer when the model is loaded.
|-
||IndexInCase (Integer)
||Index (running) number of the event in the case (ordered temporally). The first event has index number 0.
|-
||IncomingFlowOccurrences (FlowOccurrence*)
||(Available in QPR ProcessAnalyzer 2018.1) All FlowOccurrencies that end to this Event.
|-
||Model (Model)
||Model the event belongs to.
|-
||NextInCase (Event)
||Temporally next event in the case. For the last event, return EMPTY.
|-
||PreviousInCase (Event)
||Temporally previous event in the case. For the first event, return EMPTY.
|-
||OutgoingFlowOccurrences (FlowOccurrence*)
||(Available in QPR ProcessAnalyzer 2018.1) All FlowOccurrencies that start from this Event.
|-
||TimeStamp (DateTime)
||Timestamp of the event.
|-
||Type (EvenType)
||Event type of the event.
|}


{| class="wikitable"
Expressions can be chained together two ways:
!'''Event&nbsp;functions'''
* '''Contextless chaining''': When '''.''' keyword is used to chain expressions, the resulting objects will not have any context information.
! '''Parameters'''
* '''Hierarchical chaining''': When ''':''' keyword is used to chain expressions, only the result of the whole chained expression will consist of hierarchical arrays where all the values in the first expression (=context object) will be bound to the arrays those values generated. If the second expression does not return an array, the result will be changed to be an array.
! '''Description'''
|-
|| Attribute (object)
||
* attribute name (string)
|| Return event attribute value. Event attribute name is provided as a parameter. This function is needed when there are spaces in the event attribute name. If there are no spaces, syntax '''.attributeName''' can be used.
|}


=== EventLog ===
Examples:
<pre>
Contextless chaining: First expression not an array, second expression not an array:
"1".("Number is " + _)
Returns:
"Number is 1"


EventLog is a list of events that is a result of a filtering operation. Also Model contain an EventLog composing of the whole model contents. i.e. filters have been applied yet. EventLogs can be fetched by the filter id using function '''EventLogById(filterId)'''.
Contextless chaining: First expression is an array, second expression not an array:
[1,2,3].("Number is " + _)
Returns:
["Number is 1", "Number is 2", "Number is 3"]


{| class="wikitable"
Contextless chaining: First expression is an array, second expression is an array:
!'''EventLog properties'''
[1,2,3].["Number is " + _, "" + _ + ". number"]
! '''Description'''
Returns:
|-
[ ["Number is 1", "1. number"], ["Number is 2", "2. number"], ["Number is 3", "3. number"] ]
||CaseAttributes (AttributeType*)
||Used case attribute in the EventLog.
|-
||Cases (Case*)
||Cases that belong to the EventLog.
|-
||EventAttributes (AttributeType*)
||Used event attributes in the EventLog.
|-
||Events (Event*)
||Events that belong to the EventLog.
|-
||EventTypes (EventType*)
||EventTypes in the EventLog.
|-
||Flows (Flow*)
||Flows that the part of the EventLog.
|-
||Id
||EventLog Id.
|-
||Model (Model)
||Model where the EventLog belongs.
|-
||Name
||EventLog name.
|-
||Variations (Variation*)
||Variations that are in the EventLog
|}


=== EventType ===
Hierarchical chaining: First expression is an array, second expression is an array:
[1,2,3]:["Number is " + _, "" + _ + ". number"]
Returns:
[ HierarchicalArray(1, ["Number is 1", "1. number"]), HierarchicalArray(2, ["Number is 2", "2. number"]), HierarchicalArray(3, ["Number is 3", "3. number"]) ]


{| class="wikitable"
</pre>
!'''EventType properties'''
! '''Description'''
|-
||Cases (Case*)
||Cases that have events of this EventType.
|-
||Count (Integer)
||Number of Events that have this EventType.
|-
||Events (Event*)
||Events of that EventType.
|-
||Id (Integer)
||EventType Id. It's generated by QPR ProcessAnalyzer when the model is loaded.
|-
||IncomingFlows (Flow*)
||All Flows that start from the EventType.
|-
||Name (string)
||EventType name.
|-
||OutgoingFlows (Flow*)
||All Flows that end to the EventType.
|-
||UniqueCount (Integer)
||Number of Cases having events of this EventType.
|}


=== Flow ===
* '''Hierarchical arrays''': Whenever traversing a relation in expression language using hierarchical chaining operator ':' for chaining expressions, a hierarchical array will be returned. It is an object which behaves just like a normal array except it stores also context/root/key/label object which usually represents the object from which the array originated from, for example the original case object when querying events of a case.
* '''Hierarchical objects''': Arrays where at least one object in the array is itself an array is considered to be a '''hierarchical object'''. Hierarchical arrays are treated in similar way as normal arrays in hierarchical objects.
* '''Depth''' of a hierarchical object is the number of inner arrays that there are in the object, i.e. how deep is the hierarchy.
* '''Level''' in hierarchical object consists of all the nodes that are at specific depth in object's array hierarchy. '''0''' is the level at the root of the object, consisting only of the object itself as single item. Levels increase when moving towards leaves.
* '''Leaf level''' is a level that doesn't have any sub levels.


Flow is a combination of two EventTypes where there are FlowOccurrences between them. Unlike FlowOccurrencies, a Flow is not related to a single case. Flowchart shows Flows and EventTypes (not FlowOccurences or Events). In a Case, the may be several FlowOccurrences of a single Flow.
<pre>
In the following example, the second parameter of the IsConformant function is a hierarchical objects used as key-value pair collection:
EventLog.Cases:IsConformant(myDesignModel, #{"IgnoreEventTypesMissingInModel": false, "IgnoreIncompleteCases": true})
</pre>


{| class="wikitable"
== Full and limited modes ==
!'''Flow properties'''
The expression language can run in two modes: the ''full'' and ''limited'' mode. In the full mode, all functionality is available, while in the limited mode, operations that modify data or connect to external datasources are prevented. In dashboards, the limited mode is in use for security reasons, and in scripts the full mode is available. The following operations are prevented in the limited mode:
! '''Description'''
* SendEmail()
|-
* CallWebService()
||Cases (Case*)
* ImportOdbc()
||Cases that contain the flow, i.e. there is a flow occurrence between Flow's starting and ending events.
* ImportOdbcSecure()
|-
* Model.DeletePermanently()
||FlowOccurrences (FlowOccurrence*)
* Model.Restore()
||Flow occurrences the flow belongs to.
* Model.TriggerNotifications()
|-
* Project.DeletePermanently()
||From (EventType)
* Project.Restore()
||EventType from which this Flow starts.
* Project.CreateDatatable()
|-
* Datatable.AddColumn()
||Id (Integer)
* Datatable.DeletePermanently()
||Flow Id. It's generated by QPR ProcessAnalyzer when the model is loaded.
* Datatable.Import()
|-
* Datatable.Merge()
||Name (String)
* Datatable.RemoveColumns()
||Identifying name of the Flow.
* Datatable.RenameColumns()
|-
* Datatable.Truncate()
||To (EventType)
* RecycleBin.DeletePermanently()
||EventType to which this Flow ends.
* Call SQL script
|}


=== FlowOccurrence ===
Calling expression scripts is allowed, but in the script all previously mentioned operations are prevented.


FlowOccurrence represents a transition from an event to another event in a case. Thus, FlowOccurrence is related to a single case. There is also a FlowOccurrence from the "start" of the case to the first event of the case, and a FlowOccurrence from the last event of the case to the "end" of the case. Corresponding flow is visible in BPMN kind of flowcharts showing separate start and event icons. Thus, there are one more FlowOccurrences in a case than the number of events.
== In-memory expression blocks in SQL expressions ==
It's possible to embed in-memory expressions into SQL expressions using '''#expr{}''' tags. The in-memory expressions are evaluated first and results placed into the SQL expression in place of the tag as an SQL literal value, and then the SQL expression is executed in the datasource. As the in-memory expression is run in the QPR ProcessAnalyzer server, it's not possible to use the in-memory expression to process data that is located in the datasource.


{| class="wikitable"
Following data types are supported in the replaced literals: string, integer, float, boolean and date. Also if the in-memory expression returns an SQL expression, it is placed as such to the contained SQL expression. Note that the #expr{} tags are only supported in the SQL expressions.
!'''FlowOccurrence properties'''
! '''Description'''
|-
||Case (Case)
||Case the current FlowOccurrence belongs to.
|-
||Flow (Flow)
||Corresponding Flow of the current FlowOccurrence.
|-
||From (Event)
||Event where the current FlowOccurrence starts.
|-
||Id (Integer)
||FlowOccurrence Id. It's generated by QPR ProcessAnalyzer when the model is loaded.
|-
||Name (String)
||Identifying name of the current FlowOccurrence.  
|-
||OccurrenceIndex (Integer)
||Number tells how many times the current FlowOccurrence has occurred in the case until that point.
|-
||To (Event)
||Event where the this FlowOccurrence ends.
|}


=== Model===
Example:
<pre>
function GenerateExpression(increment) {
  return ToSqlExpression(#sql{Column("id") + #expr{increment}});
}
df.WithColumn("foo", #expr{GenerateExpression(1)} + #expr{GenerateExpression(2)} + 1).Collect()


{| class="wikitable"
Will add a column for dataframe having value equal to SqlExpression: (Column("id") + 1) + (Column("id") + 2) + 1
!'''Model properties'''
</pre>
! '''Description'''
|-
||CaseAttributes (AttributeType*)
||CaseAttributes of the model.
|-
||DefaultFilterId (Integer)
||Id of the default filter for the model.
|-
||EventAttributes (AttributeType*)
||EventAttributes of the model.
|-
||EventLog (EventLog)
||EventLog of the model. Model EventLog contains all events of the model, i.e. no filters have been applied.
|-
||Id (Integer)
||Model Id. Model Id is generated by QPR ProcessAnalyzer when the model is created.
|-
||Name (String)
||Name of the model.
|-
||Project (Project)
||The Project the this model belongs to.
|}
 
=== TimeSpan ===
 
TimeSpan represents a temporal duration (for example: 5 hours or 8 days). TimeSpan is not bound to calendar time. Difference between two TimeStamps is TimeSpan. TimeStamp added by TimeSpan is TimeStamp.
 
{| class="wikitable"
!'''TimeSpan properties'''
! '''Description'''
|-
||TotalDays (Float)
||Timespan value in days (one day is 24 hours) (both whole and fractional).
|-
||TotalHours (Float)
||Timespan value in hours (both whole and fractional).
|-
||TotalMilliseconds (Float)
||Timespan value in milliseconds (both whole and fractional).
|-
||TotalMinutes (Float)
||Timespan value in minutes (both whole and fractional).
|-
||TotalSeconds (Float)
||Timespan value in seconds (both whole and fractional).
|}
 
=== Project ===
{| class="wikitable"
!'''Project properties'''
! '''Description'''
|-
||Id (Integer)
||Id of the Project.
|-
||Name (String)
||Name of the Project.
|-
||Models (Model*)
||Models that belong to this Project.
|}
 
=== Variation ===
{| class="wikitable"
!'''Variation properties'''
! '''Description'''
|-
||CaseCount (Integer)
||Number of cases in the variation.
|-
||Cases (Case*)
||Cases that belong to the variation.
|-
||EventTypeCount (Integer)
||Number of events in the variation.
|-
||EventTypes (EventType*)
||Event types belonging to the variation.
|-
||Flows (Flow*)
||(Available in QPR ProcessAnalyzer 2018.1) Flows that belong to the variation.
|-
||Id (Integer)
||Variation Id. It's generated by QPR ProcessAnalyzer when the model is loaded.
|-
||UniqueEventTypeCount (Integer)
||Number of different (unique) event types in the variation.
|}


== Expression Examples ==
[[Category: QPR ProcessAnalyzer]]
See expression examples: [[QPR ProcessAnalyzer Expression Examples]].
[[Category: QPR UI]]

Latest revision as of 17:35, 7 July 2024

QPR ProcessAnalyzer expression language is a versatile query engine for all the QPR ProcessAnalyzer data and models. The expression language can perform variety of calculation operations, such as aggregations, dimensioning and following relations between objects.

Expression Language Overview

The picture below is an overview of all the major areas of the QPR ProcessAnalyzer expression language.

ExpressionLanguageOverview.png

The expression language has the following areas:

  • Generic types cover the basic data structures familiar from many programming languages.
  • Configuration objects API allows to access the objects used to manage the content in QPR ProcessAnalyzer system. All objects are organized into projects which contains dashboards, models, datatables and scripts. In addition, models contain filters. Note that dashboards are not yet supported by the configuration objects API. There are also users and groups.
  • Machine learning allow to perform machine learning libraries calculated by the QPR ProcessAnalyzer system.
  • In-memory model API is the interface in the backend for the process mining calculations performed in the QPR ProcessAnalyzer Server memory. When the in-memory model API, the model needs to be loaded into the QPR ProcessAnalyzer Server memory and only after that the calculations can be done.
  • DataFlow is used to read data from a datasource, transform the data and load it to a destination which are the basic steps of ETL. The core idea of the DataFlowis that the data is processed in small chunks, allowing to handle unlimited amount of data without large computing resources. Note that the DataFlow can only do transformations that can be done row by row, such as adding new column. For example, sorting the data is not possible, as it would require to load the entire dataset to be able to perform the sorting operation. The source of the data can be an external system where the extract the data or QPR ProcessAnalyzer's own datatables for further processing previously extracted data.
  • DataFrame capsulates tabular datasets stored and processed in QPR ProcessAnalyzer Server memory. There is versatile set of functionality available to transform the data. Using the DataFrame requires that the entire dataset is loaded into the memory which sets certain limits how large datasets can be processed.
  • SqlDataFrame is conceptually similar to the DataFrame except the data is not in the memory but it's located in the datasource where it's stored (either Snowflake or SQL Server). The transformations are in practice done using SQL that is processed by the datasource itself. Using the SqlDataFramedoesn't pose any requirements to the QPR ProcessAnalyzer Server itself as the data never ends to there. Instead Snowflake warehouse size or SQL Server capacity is an important factor how large datasets can be managed.
  • SQL Expressions are part of the SqlDataFrame and they are expressions to make different kind of calculations to the data. Similar to the SqlDataFrame, the SQL Expressions are converted into SQL for the actual calculation in the datasource.

In the dashboards, there are two chart types available:

  • Snowflake chart creates a Snowflake query to fetch the data. The in-memory query uses the SqlDataFrame to perform calculations.
  • In-memory chart creates an in-memory query to fetch the data. The in-memory query uses the In-memory model API to perform calculations. Thus, all the features and limitation of the In-memory model API affect the in-memory chart.

Expression language is run by QPR ProcessAnalyzer Server, and the expressions are used by dashboards and scripts.

Introduction to Expressions

  • Expression is sequence of calculation instructions written in a textual form, which are calculated by QPR ProcessAnalyzer Server.
  • Expressions may be broken down into sub-expressions which are calculated as part of the main expression calculation.
  • Calculating an expression always gives a result which may be any type of object.
  • Expressions are always calculated within some context.
  • Context can be accessed explicitly in an expression by using keyword _ (underscore).
  • Expression language is an object oriented, containing entities for all process mining objects (e.g. cases, events, variations) and also objects for managing content in QPR ProcessAnalyzer (models, projects, datatables).
  • Each object has an own context in which object's functions and properties can be used.
  • There is also the generic context which is used when the function or property is not found in the object specific context.
  • If there is both a variable and a property with the same name in the same scope, the variable has the precedence, i.e., an identifier refers to the variable, not the property.

Chaining Expressions

Two expressions can be chained together using the dot (.) operator. Depending on the types of the objects (whether they are arrays or scalar values) on the left and right side, the result varies, and it's summarized in the table below. Calculation functions as follows:

  • If the first expression result is a scalar (i.e. not an array), the second expression will be calculated with the first expression result as its context.
  • If the first expression result is an array, the second expression will be calculated for each left side array item as context. The result will be an array of calculation results.
  • If additionally the second expression calculations return arrays, the resulting object will be an array of arrays, i.e. a new array is created for each of the left side array items.
  • If the left side expression is not an array, the calculation return a normal one-dimension array.
Left side Right side Result
scalar scalar scalar
scalar array array
array scalar array
array array two-dimensional array

Examples:

The left side is a string (i.e. not an array), so that string will be the context for the expression after the dot:
"one".("Number is " + _.ToUpper())
Result: Number is ONE

The left side is an array, so the right side expression will be calculated for each of the array items:
["one", "two", "three"].("Number is " + _.ToUpper())
Result (array of three strings): ["Number is ONE", "Number is TWO", "Number is THREE"]

Both the left and right sides are arrays, so the results is an array of arrays:
["one", "two", "three"].(["Number is " + _.ToUpper(), "First letter is " + _.Substring(0,1)])
Result (array of arrays containing strings):
[
	["Number is ONE", "First letter is o"],
	["Number is TWO", "First letter is t"],
	["Number is THREE", "First letter is t"]
]

This is same as the previous, except an aggregation function is applied, which aggregates the deepest level:
StringJoin(", ", ["one", "two", "three"].(["Number is " + _.ToUpper(), "First letter is " + _.Substring(0,1)]))
Result: ["Number is ONE, First letter is o", "Number is TWO, First letter is t", "Number is THREE, First letter is t"]

Sum([1, 2, 3].([_ + 4, _ + 5]))
Result: [11, 13, 15]

Average(Sum([1, 2, 3].([_ + 4, _ + 5])))
Result: 13

let myCases = EventLogById(1).Cases
Average(myCases.Duration)
Result: Average duration of all cases in the EventLog 1.

let myCases = EventLogById(1).Cases
Average(
	Count(
		myCases.{
			let lastEvent = LastEvent.Typename;
			Events.Where(Typename==lastEvent)
		}
	)
)

Operators

Several expressions can be separated using semicolon (;). Example:

let var1 = 2;
let var2 = 3;
var1 + var2;
Returns: 5

Expression can contain line breaks without affecting the calculation.

Following arithmetic and logical operations are available:

  • Addition (+) can be performed on numeric types, strings (concatenate two strings) and Timespans (addition of two TimeSpans).
  • Subtraction (-) can be performed on numeric types, DateTimes (calculating TimeSpan between two DateTimes) and TimeSpans (difference between two TimeSpans).
  • Multiplication (*) can be performed on numeric types, and between a TimeSpan and a numeric type (multiplying TimeSpan by number which results in TimeSpan),
  • Division (/) can be performed on numeric types, between a TimeSpan and a numeric type (dividing a TimeSpan where TimeSpan must be the left hand side operand).
  • Remainder (%): can be performed on numeric types.
  • Comparison operators ==, <, <=, >, >=, != are used to compare objects and return boolean values.
  • Logical operators: && (AND), || (OR), ! (NOT) are used to combine expressions that return boolean values.

There is quick syntax for condition statement available:

condition ? trueValue : falseValue

Example:

var1 == 4 ? "Value is 4" : "Value is other than 4"

Common Datatypes

The expression language has the following basic datatypes available:

Data type Expression language type Literal expression Use cases How to convert
String String
"Hello world!"
""
"Characters to escape: \" and \\"
"Line 1\nLine2"
Strings are textual values. The usual mistake is to store numbers and dates as strings which makes difficult to make calculations and comparison with them. To convert variable var1 value to a string, use expression:
let var1 = 295.01;
ToString(var1);
Integer Int32 or Int64
123456
-1234
0
Integers are whole numbers that describe numbers that don't have decimals, e.g. number of items etc. Equality comparisons with integers are possible. To convert variable var1 value to an integer, use expression:
let var1 = "29501";
ToInteger(var1);
Decimal number Double
123.456
0.123
-12.34
1.5675E4
35E-6
Decimal numbers (expression language data type is double) may have decimals. Note that equality comparisons are not possible with decimal numbers. To convert variable var1 value to a decimal number, use expression:
let var1 = "295.01";
ToFloat(var1);
DateTime DateTime
DateTime(2021, 5, 21, 15, 30, 13, 482)
Dates are for the timestamps. If data is as string, the string needs to be splited into different parts, convert them into integers and DateTime function used. Example:
let var1 = "2020-10-21T11:48:04.842";
let splitted=var1.Split(["-", "T", ":", "."]).ToInteger(_);
DateTime(splitted[0],splitted[1],splitted[2],splitted[3],splitted[4],splitted[5],splitted[6]);
Boolean Boolean
true
false
Booleans can have either true or false. If data is in a string, compare it agains a string literal "true". Example:
var1=="true"
Duration Timespan

(no literal presentation)

Duration between two timestamps. Timespan() or TimespanFromTicks() functions.
Dictionary/JSON Dictionary/JSON
#{"name": "John", "age": 30, "children": ["Anna", "Mia", "Eric"]}
#{"number": 1, "text": "Hello!", "datetime": DateTime(2020)}
#{1: "number", "Hello!": "text", DateTime(2020): "datetime"}

Notes:

  • Variables and datatable cells may also contain null values, which usually designate missing values etc.
  • Expression language data type for an object is seen using InternalTypeName property

Notes on string literals:

  • Characters " (double quote) and \ (backslash) need to be escaped using the \ (backslash) character.
  • Linebreaks can be added with \n and tabulator with \t.
  • Unicode characters can be added with \uXXXX where XXXX is a unicode character code, example: \u001f.

For writing date and timespan value literals, use the DateTime and Timespan functions.

Arrays

Array is an object that is a list of any types of objects. Arrays can be created in an expression by enclosing a comma-separated list of items to brackets.

Examples:

[1, 2, 3]
Returns: An array with three elements: 1, 2 and 3.

["Dallas", "New York", "Los Angeles"]
Returns: Array of strings (region names).

[]
Returns: An empty array.

It's possible to apply operators (such as +, -, *, /, %, >, >=, <, <=, !=, ==) directly to arrays with the following rules:

  • If both operands are arrays, the operator is applied separately for each item in the arrays in a way that items at the same index are applied with each other. If the lengths of arrays are different, an exception is thrown.
  • If only the left or right side operator is an array, the operator is applied for each item in the array together with the non-array operand.
  • If both operands are not arrays, the operator is applied directly to the objects.

Examples:

[1,2,3] + [4,5,6]
Returns: [5,7,9]

[1,2,3] > 2
Returns: [false,false,true]

[1,2,null] ?? 3
Returns: [1,2,3]

[[1,2,3],[5,6,7]] + [1,2]
Returns: [[2,3,4],[7,8,9]]

Arrays can also be used directly with those operators having one operand (-, !, ~). Examples:

-[1,2,3]
Returns: [-1,-2,-3]

![true, [false, true], false]
Returns: [false,[true, false],true]

![0, [1, 0], 1]
Returns: [true, [false, true], false]

Logical operators (&& and ||) don't support arrays in the way described previously. Also, the null coalescing operator (??) supports arrays only for left side operand, whereas the right side operand cannot be an array.

Lookup operator ([ ])

Lookup operator (brackets) is used to get one or several items from an array or a dictionary. Using the brackets, it's possible to define:

  • single integer to get a single item from the array
  • array of integers to get multiple items from the array

Note that the indices start from the zero, and using an index which is not in the array will throw an exception.

In addition to static values, it's possible to defined a lookup expression inside the brackets. The lookup expression is evaluated in the context of the array where items are to be fetched.

Examples:

[1, 2, 3, 4][1]
Returns: 2

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

["a", "b", "c", "d"][[0, Count(_) - 1]]
Returns: ["a", "d"]

[[1, 2], 3, [4, [5, 6]]][2][1][0]
Returns: 5

The lookup operator also works for dictionaries:

Examples:

#{"a": 1, "b": 2, "c": 3}["b"]
Returns: 2

#{1: "number", "Hello!": "text", DateTime(2020): "datetime"}[[DateTime(2020), "Hello!", 1]]
Returns: ["datetime", "text", "number"]

Also this kind of lookup can be used:

#{"a": 1, "b": 2, "c": 3}.b
Returns: 2

#{"a": 1, "b": 2, "c": 3}.(a+b+c)
Returns: 6

Define variables (let) and assign variable values (=)

Variables can be defined using the let operator:

let variableName = variableValue;

Variables can only be used in the same scope where they are defined. Variable cannot be initialized, if there is already a variable with the same name in the same scope.

Examples:

let myVariable1 = "myValue";
let myVariable2 = Now;
let myVariable3 = 4;

Variables can be defined without the initial value. In that case, the variables get and _empty value. Example:

let myVariable1;

It's possible to assign (set) values to variables using the following syntax:

variableName = variableValue;

Examples:

myVariable1 = "new value";
myVariable2 = Now;
myVariable3 = myVariable3 + 1;

Note that the variables need to be defined first to be able to set values for them.

Conditional statement (if)

Conditions can be written using the "if" statement which has the following syntax:

if (condition) {
  //run if condition is true
} else {
  //run if condition is false
}

The "else" block is not mandatory:

if (condition) {
  //run if condition is true
}

It's also possible to chain if's:

if (condition1) {
  //run if condition1 is true
} else if (condition2) {
  //run if condition2 is true (and condition1 false)
} else if (condition3) {
  //run if condition3 is true (and condition1 and condition2 false)
} else {
  //run if all of the above conditions are false
}

Conditional statement (switch)

Conditions can be written using the "switch" statement, following the JavaScript syntax (https://www.w3schools.com/js/js_switch.asp). Switch statement is more limited than the "if" statement, but the switch is easier to read and performs faster than the "if" statement (in cases when it can be used). Also the "break" and "default" statements are usually used with the switch. Switch statement has the following syntax (where expr, x and y are expressions):

switch (expr) {
  case x:
    // code block
    break;
  case y:
    // code block
    break;
  default:
    // code block
}

Example: dimension expression where a case attribute value is mapped to a textual presentation.

let label;
switch (Attribute("Shopping Cart Type")) {
  case "Standard":
    label = "Shopping cart type is standard";
    break;
  case "Fast track":
    label = "Shopping cart type is fast track";
    break;
  case null:
    label = "Shopping cart type is not defined";
    break;
  default:
    label = "Shopping cart type is other";
}

Looping statement (for)

The for loop can be defined using the following syntax:

for (initialization; condition; iterator) {
  statements;
}

The initialization is evaluated in the beginning, and the iterator is evaluated after each iteration. The loop continues to the next iteration if the condition is true.

Example (returns 10):

let sum = 0;
for (let i = 1; i < 5; i++) {
  sum = sum + i;
}
return sum;

Looping statement (while)

The while loop can be defined using the following syntax:

while (condition) {
  //looped as many times the condition is true
}

Example: loop is stopped when the counter variable reaches 5:

let counter = 0;
while (counter < 5) {
  counter = counter + 1;
}
counter;

In addition, break (exit the while loop) and continue (start the next iteration) statements can be used with the while loop.

This example using break prints "12":

let items = "";
let i = 0;
while (i < 5) {
  i = i + 1;
  if (i == 3) {
    break;
  }
  items = items + i;
}
items;

This example using continue prints "1245":

let items = "";
let i = 0;
while (i < 5) {
  i = i + 1;
  if (i == 3) {
    continue;
  }
  items = items + i;
}
items;

Return statement

It's possible to return from the current block with a value using the return statement. Example:

return "value to return";

The return value is optional. If not given, the _empty value is returned. Example:

return;

The return statement works as follows in the different circumstances:

  • If currently evaluating an user defined function, then the return value is returned to the caller of the user defined function as function call result.
  • If currently evaluating a chained expression, the return value is used as the result of the chained evaluation.
  • If currently evaluating an argument for a function call, then the return value is used as the value of the parameter being evaluated.
  • Otherwise returns the value as the result of the evaluation of the whole expression.

Null conditional operator (?.)

The null conditional operator (one question mark) is useful in chaining operations where there might appear null values in the middle of the chain. If not handled correctly, e.g. trying to get a property from a null value, will throw an exception. In the null conditional operator, if the result of the left-hand side expression is a null value, the next step in the chaining operation is not executed, but a null value is returned instead.

For example, the following expression throws an exception if StartTime is null:

StartTime.Truncate("month")

The null conditional operator can be used to take into account the null situation, and the following expression returns null if StartTime is null:

StartTime?.Truncate("month")

In the null conditional operator, if the left-side expression is an array or a hierarchical array, the chaining operator does not chain null values in that array (see the examples). Null conditional chaining can be applied to both contextless and hierarchical chaining operations by prefixing the chaining operator with ? character.

The null conditional operator is faster to calculate and syntax is easier to read than using if condition.

Examples:

[DateTime(2020, 3, 15), null]?.truncate("year")
Returns: [DateTime(2020), null]
(would return error without the null conditional operator)

There is also a null conditional lookup operator which can be applied to the lookup operation by adding ? character in the front of the lookup operator. If used, and there is a null value instead of an array, the result of the lookup operation is null (an exception would be thrown without the null conditional lookup).

Examples:

null?[3]
Returns: null

[[1, 2], null, [3]].(_?[0])
Returns: [1, null, 3]

Null coalescing operator (??)

The null coalescing operator (two question marks) can be used to replace null values with something else. The null coalescing operator works as follows:

  • If the left-hand side expression is null, the right-hand side value is returned.
  • If the left-hand side expression is not null, the left-hand side value is returned.

The null coalescing operator combined with the _remove operator is one way to remove null values from an array (see the examples below), but the recommended way is to use the RemoveNulls function.

Examples:

null ?? "foo"
Returns: "foo"

1 ?? "foo"
Returns: 1

[1, null, 3].(_ ?? "foo")
Returns: [1, "foo", 3]

[1, null, 3].(_ ?? _remove)
Returns: [1, 3]

[1, null, 3]:(_ ?? _remove)
Returns: [1: [1], 3: [3]]

Increment and decrement operators

Increment (++) and decrement (--) operators serve as a short syntax to increase/decrease numeric value by one. The operators can be used before (prefix) or after (postfix) the incremented/decremented variable. When used as prefix, the changed variable value is returned by the increment/decrement statement (i.e., it can be seen that the increment/decrement is made before the other operations in the same row are made). When used as postfix, the original variable value is returned by the increment/decrement statement (i.e., it can be seen that the increment/decrement is made after the other operations in the same row are made).

Examples:

let myVar1 = 1;
let myVar2 = myVar1++; // myVar2 = 1
let myVar1 = 1;
let myVar2 = ++myVar1; // myVar2 = 2
let myVar1 = 1;
let myVar2 = myVar1--; // myVar2 = 1
let myVar1 = 1;
let myVar2 = --myVar1; // myVar2 = 0
let counter = 0;
while (counter++ < 3) {
	WriteLog(counter); // Writes to log: 1, 2, 3
}
let counter = 0;
while (++counter < 3) {
	WriteLog(counter); // Writes to log: 1, 2
}

Catching exceptions

The try...catch statement is used to catch thrown exceptions, to handle the exception situation and continue executing script despite the exception. The statement comprises of a try block and either a catch block, a finally block, or both. The code in the try block is executed first, and if it throws an exception, the code in the catch block is executed. The code in the finally block will always be executed before control flow exits the entire construct.

try...catch statement structure:

try {
  tryStatements
} catch (exceptionVariable) {
  catchStatements
} finally {
  finallyStatements
}

There are following blocks in the above example:

  • tryStatements: Statements to be executed.
  • catchStatements: Statements that are executed if an exception is thrown in the try block.
  • exceptionVariable: Optional identifier to hold the caught exception for the associated catch block.
  • finallyStatements: Statements that are executed before control flow exits the construct. These statements execute regardless of whether an exception was thrown or caught.

Example to catch errors in data extraction, log the error and continue script execution:

try {
  results = ExtractSap(queryParameters);
} catch (error) {
  Writelog("Data extraction from SAP failed.");
}

Throwing objects

Objects can be thrown using the throw statement. For example, strings can be thrown, but the thrown object can also be a more complex dictionary containing additional data. Note that there is no separate exception entity that can be thrown.

Following example throws a string that can contain a message why the processing was stopped:

throw "Error occurred etc.";

The thrown object can be caught using the try-catch statement. The caught entity is an exception object that has the thrown object in parameter thrownObject.

Example:

try {
  throw "Error occurred etc.";
} catch (exception) {
  WriteLog(exception.thrownObject);
}

Defining functions

Functions can be defined using following syntax (called lambda syntax):

(param1, param2, param3) => (function definition)

Examples:

Function to add to numbers:
(a, b) => a + b

Function to return the current time:
() => Now

FunctionDefinition encapsulates a function consisting of following parts:

  • function body (expression)
  • function parameters (optional)
  • function name.

FunctionDefinition objects can be created in the following ways:

  • Using function <function name>(<function arguments>)<function body> syntax
  • Using (<function arguments>) => <function body> syntax
  • Using Def function
  • In Def function, use &-prefix is for an attribute

Functions defined by a FunctionDefinition object can be called as follows: <function name>(<function arguments>). Functions are evaluated in a new scope, i.e. variables defined in the function are only available within that function.

Examples:

Def(null, "a", "b", a+b)._(1,2)
((a, b)=>a+b)._(1,2)
((a, b)=>a+b)(1,2)
Returns: 3

[Def("", "a", "b", a+b), Def("", "a", "b", a*b)].(_(1,2))
[(a, b)=>a+b, (a, b)=>a*b].(_(1,2))
Returns: [3, 2]

Def("FirstOrNull", "arr", CountTop(arr) > 0 ? arr[0] : null)
function FirstOrNull(arr) { CountTop(arr) > 0 ? arr[0] : null }
Result: Are all equivalent and create a function FirstOrNull into the current evaluation scope. Returns also the created FunctionDefinition object.

The FunctionDefinition objects have the following properties:

  • Arguments: Returns an array containing names of all arguments of the function.
  • Body: Expression body of the function as string.
  • Name: Name of the function if this is a named function.

It's possible to define functions that call themselves, which is used in recursive algorithms. There is a certain limit how many times a function can call other functions. Usually this limit may be reached with functions calling themselves. If this stack overflow situation is encountered, there may be a bug in the algorithm or then there are too many recursions for the system to handle.

Template strings

Template strings are string literals enclosed with backticks (`) that can contain embedded expressions defined using syntax ${expression}. The embedded expressions are evaluated in the same calculation context in which the template string is defined.

Notes on template strings:

  • If there is a need to use backticks in the template strings, escaping is done with a backslash (\`).
  • Template strings can contain line breaks.
  • Embedded expressions can also contain template strings.

Examples:

let creditBlocksCount = 15;
`There are total of ${creditBlocksCount} credit blocks!`;
Returns: There are total of 15 credit blocks!

EventLogById(1).Cases.`Case ${Name} contains ${Count(Events)} events`
Returns a list of following strings for each case, e.g. Case 12345 contains 12 events

`${1+3}`
Returns: 4

`${`${1+1}`}`
Returns: 2

Models.`Id:${Id}, Name: ${Name}`
Returns: An array of strings containing model ids and names.

let m = [
  #{"Name":"Model 1", "Id": "1", "Description": "Big one"},
  #{"Name":"Model 2", "Id": "2", "Description": "This too"},
  #{"Name":"Foo Model", "Id": "3", "Description": "Not this"}
];
`<ul>${StringJoin("""", OrderBy(m, Name).`
  <li>
    Model: ${Id == 2 ? `(default) ${Name}`: Name}
    Id: ${Id}
    Description: ${Description}
  </li>`)}
</ul>`

Returns:
<ul>
  <li>
    Model: Foo Model
    Id: 3
    Description: Not this
  </li>
  <li>
    Model: Model 1
    Id: 1
    Description: Big one
  </li>
  <li>
    Model: (default) Model 2
    Id: 2
    Description: This too
  </li>
</ul>

Code comments

Single line comments can be added using // syntax. Line comment spans until the end of the line.

let var1 = 123; //This is comment which is ignored by the calculation

Multiline comments can be added with syntax starting with /* and ending to */.

/*
Comment that spans to
multiple lines.
*/

Expression Chaining using : keyword

Expressions can be chained together two ways:

  • Contextless chaining: When . keyword is used to chain expressions, the resulting objects will not have any context information.
  • Hierarchical chaining: When : keyword is used to chain expressions, only the result of the whole chained expression will consist of hierarchical arrays where all the values in the first expression (=context object) will be bound to the arrays those values generated. If the second expression does not return an array, the result will be changed to be an array.

Examples:

Contextless chaining: First expression not an array, second expression not an array:
"1".("Number is " + _)
Returns:
"Number is 1"

Contextless chaining: First expression is an array, second expression not an array:
[1,2,3].("Number is " + _)
Returns:
["Number is 1", "Number is 2", "Number is 3"]

Contextless chaining: First expression is an array, second expression is an array:
[1,2,3].["Number is " + _, "" + _ + ". number"]
Returns:
[ ["Number is 1", "1. number"], ["Number is 2", "2. number"], ["Number is 3", "3. number"] ]

Hierarchical chaining: First expression is an array, second expression is an array:
[1,2,3]:["Number is " + _, "" + _ + ". number"]
Returns:
[ HierarchicalArray(1, ["Number is 1", "1. number"]), HierarchicalArray(2, ["Number is 2", "2. number"]), HierarchicalArray(3, ["Number is 3", "3. number"]) ]

  • Hierarchical arrays: Whenever traversing a relation in expression language using hierarchical chaining operator ':' for chaining expressions, a hierarchical array will be returned. It is an object which behaves just like a normal array except it stores also context/root/key/label object which usually represents the object from which the array originated from, for example the original case object when querying events of a case.
  • Hierarchical objects: Arrays where at least one object in the array is itself an array is considered to be a hierarchical object. Hierarchical arrays are treated in similar way as normal arrays in hierarchical objects.
  • Depth of a hierarchical object is the number of inner arrays that there are in the object, i.e. how deep is the hierarchy.
  • Level in hierarchical object consists of all the nodes that are at specific depth in object's array hierarchy. 0 is the level at the root of the object, consisting only of the object itself as single item. Levels increase when moving towards leaves.
  • Leaf level is a level that doesn't have any sub levels.
In the following example, the second parameter of the IsConformant function is a hierarchical objects used as key-value pair collection:
EventLog.Cases:IsConformant(myDesignModel, #{"IgnoreEventTypesMissingInModel": false, "IgnoreIncompleteCases": true})

Full and limited modes

The expression language can run in two modes: the full and limited mode. In the full mode, all functionality is available, while in the limited mode, operations that modify data or connect to external datasources are prevented. In dashboards, the limited mode is in use for security reasons, and in scripts the full mode is available. The following operations are prevented in the limited mode:

  • SendEmail()
  • CallWebService()
  • ImportOdbc()
  • ImportOdbcSecure()
  • Model.DeletePermanently()
  • Model.Restore()
  • Model.TriggerNotifications()
  • Project.DeletePermanently()
  • Project.Restore()
  • Project.CreateDatatable()
  • Datatable.AddColumn()
  • Datatable.DeletePermanently()
  • Datatable.Import()
  • Datatable.Merge()
  • Datatable.RemoveColumns()
  • Datatable.RenameColumns()
  • Datatable.Truncate()
  • RecycleBin.DeletePermanently()
  • Call SQL script

Calling expression scripts is allowed, but in the script all previously mentioned operations are prevented.

In-memory expression blocks in SQL expressions

It's possible to embed in-memory expressions into SQL expressions using #expr{} tags. The in-memory expressions are evaluated first and results placed into the SQL expression in place of the tag as an SQL literal value, and then the SQL expression is executed in the datasource. As the in-memory expression is run in the QPR ProcessAnalyzer server, it's not possible to use the in-memory expression to process data that is located in the datasource.

Following data types are supported in the replaced literals: string, integer, float, boolean and date. Also if the in-memory expression returns an SQL expression, it is placed as such to the contained SQL expression. Note that the #expr{} tags are only supported in the SQL expressions.

Example:

function GenerateExpression(increment) {
  return ToSqlExpression(#sql{Column("id") + #expr{increment}});
}
df.WithColumn("foo", #expr{GenerateExpression(1)} + #expr{GenerateExpression(2)} + 1).Collect()

Will add a column for dataframe having value equal to SqlExpression: (Column("id") + 1) + (Column("id") + 2) + 1