QPR ProcessAnalyzer Objects in Expression Language: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
 
(311 intermediate revisions by 2 users not shown)
Line 1: Line 1:
== DataFrame ==
==Filter==
 
Filters contain a set of filter rules used to filter cases and events in models. Filters are objects located in the models. Filters are owned by the creator user, and when a filter publish mode is private, only the creator can use it.
DataFrame represents a two dimensional array of data with one-to-many columns and zero-to-many rows, like a relational database table, an Excel sheet or a CSV data file. Each column in the DataFrame has a name, and there must not be more than one column with the same name. DataFrame is the generic data structure used to manage all kinds data in QPR ProcessAnalyzer expression engine that run in-memory. DataFrames as linked to other entities in QPR ProcessAnalyzer as follows:
* Datatable contents is fetched into the memory as a DataFrame object
* DataFrame can be stored (persisted) to a Datatable
* ETL operations, such as joining, unions, filtering and grouping are based on the DataFrames
* Data extracted from an external data source, e.g. using ODBC, is fetched to the in-memory calculation as a DataFrame.
* Model can get cases and events data from a DataFrame when using an [[QPR_ProcessAnalyzer_Model_Datasources#Loading_Data_from_Expression_Datasource|expression datasource]].


{| class="wikitable"
{| class="wikitable"
!'''DataFrame properties'''
!'''Filter properties'''
! '''Description'''
! '''Description'''
|-
|-
||Columns (String*)
||CreatedBy (User)
||DataFrame columns names as an array in the order the columns are in the DataFrame.
||Returns the user who created the filter.
|-
||CreatedDate (DateTime)
||Returns date when the filter created date.
|-
||Description (String)
||Returns description of the filter.
|-
||Id (Integer)
||Returns id of the filter.
|-
||LastModifiedBy (User)
||Returns user who modified the filter.
|-
||LastModifiedDate (DateTime)
||Returns date when the filter last modified.
|-
||Model
||Returns model where the filter belongs to.
|-
|-
||Rows (Object**)
||ModelId (Integer)
||Returns the data content of the DataFrame as a two-dimensional array (matrix). The column names are not part of the data content.
||Returns model where the filter belongs to.
|-
||Name (String)
||Returns the name of the filter.
|-
||Project
||Returns project where the filter belongs to.
|-
||ProjectId (Integer)
||Returns project id where the filter belongs to.
|-
||PublishMode (String)
||Returns publish mode of the filter, one of the following: '''Private''', '''Public''', or '''Default'''.
|-
||Rules (Dictionary)
||Returns a dictionary containing the filter rules in the filter.
|}


Examples:
{| class="wikitable"
<pre>
!'''Filter functions'''
DatatableById(5).DataFrame.Rows[0][0]
!'''Parameters'''
Returns: the value in the first row and first column in a datatable with id 5.
! '''Description'''
</pre>
|-
||DeletePermanently
||(none)
||
Deletes the filter permanently. To delete own filters, the '''Filtering''' permission is needed, and to delete any filters the '''ManageViews''' permission is needed.
|-
|-
||<column name> (Object*)
||Modify
||Dictionary
||
||
Returns an array of values of given column in the datatable. If the column name contains spaces, the Column function needs to be used to refer to a column.
Modifies filter properties. The parameter is a dictionary containing the properties to be changed. Following properties can be changed: ''Name'', ''Description'', ''PublishMode'', and ''Rules''.
 
The function returns the updated filter object. Requires ''GenericWrite'' permission for the Project and global ''CreateModel'' permission.


Examples:
Example:
<pre>
<pre>
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]).right
FilterById(1)
Returns: [zero, two, three]
.Modify(#{
 
"Name": "My filter",
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]).right[2]
"Description": "My description",
Returns: three
"PublishMode": "Public",
"Rules": #{
"Items": [
#{
"Type": "IncludeCases",
"Items": [
#{
"Type": "CaseAttributeValue",
"Attribute": "Account Manager",
"StringifiedValues": [
"0Mary Wilson"
]
}
]
}
]
}
}
)
</pre>
</pre>
|-
|}
|}


Function to get filter id:
{| class="wikitable"
{| class="wikitable"
!'''DataFrame&nbsp;functions'''
!'''Functions'''
! '''Parameters'''
!'''Parameters'''
! '''Description'''
! '''Description'''
|-
|-
||Append (DataFrame)
||FilterById
||
||
* DataFrame which data to append
* Filter id (Integer)
||
||
Creates a new DataFrame that has the contents of given DataFrame added to the end of this DataFrame. When the data is combined, the order of columns matters, not the names of the columns. The resulting DataFrame gets column names from this DataFrame.
Returns Filter object corresponding to the provided filter id.
|}


If the number of columns is different between this DataFrame and the other DataFrame, an exception is thrown.
==Model==
Notes:
* For in-memory models that are offline, the object counts represent the situation when the model was last time online (loaded into the memory). ''null'' is returned if the model has never been loaded into the memory.
* If [[Case_Level_Permissions|Case permissions]] are used for the model, and user doesn't have '''GenericWrite''' permission for the model, ''null'' is returned for data security reasons. Users that have the '''GenericWrite''' permission, see null when the model is offline, and when online, they see counts where the case level permissions settings are applied.
* Properties ''CaseAttributes'', ''EventAttributes'' and ''Eventlog'' work only for the in-memory models and they require the model to be loaded into the memory. If the model is not in the memory, it is loaded when these properties is used. Other model properties down require the model to be in the memory.


Examples:
{| class="wikitable"
!'''Model properties'''
! '''Description'''
|-
||Calendars (BusinessCalendar*)
||
Returns all [[Business_Calendar|business calendars]] stored to the Model as an array. Returns an empty array, if there are no business calendars stored to the model. Note: UI allows to set only one business calendar for a Model.
|-
||CaseAttributes (AttributeType*)
||[[#AttributeType|CaseAttributes]] in the model returned in the alphabetical order. Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
|-
||CasesDatatable (Datatable)
||Returns the Datatable the model uses as a datasource for cases. Returns ''null'' if the cases Datatable is not defined or if model uses other than the Datatable datasource.
|-
||Configuration (Dictionary)
||Returns the Model configuration as dictionary. Example:
<pre>
<pre>
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "text"]).Append(
ModelById(123).Configuration.DataSource.Events.DataTableName
  ToDataFrame([[1, "one"], [4, "four"]], ["id", "text"])
);
 
Returns string:
id;text
0;zero
2;two
3;three
1;one
4;four
</pre>
</pre>
|-
|-
||ColumnIndexes (Integer*)
||ConfigurationJson (String)
||Returns the Model configuration as JSON string.
|-
||CreatedBy (User)
||User who created the model.
|-
||CreatedDate (DateTime)
||Timestamp when the model was created.
|-
||DefaultCalendar (BusinessCalendar)
||Returns the default [[Business_Calendar|business calendar]] of the Model. Returns ''null'', if there are no calendars in the Model or no calendar has been set as a default calendar. Note: UI allows to set only one business calendar for a Model, which is also the default calendar.
|-
||DefaultFilter (Filter)
||Default filter of the model. Returns ''null'' if the model does not have a default filter.
|-
||DefaultFilterId (Integer)
||Default filter id of the model. Returns ''null'' if the model does not have a default filter.
|-
||Description (String)
||Model description. The model description may contain line breaks.
|-
||DeletedDate (DateTime)
||Timestamp when Model was deleted (moved to the recycle bin).
|-
||DeletedBy (User)
||User how deleted the Model.
|-
||Diagrams (Diagram*)
||Returns an array of all [[Diagram_in_Expression_Language|diagrams]] in the model.
|-
||EstimatedMemory (Integer)
||Returns an estimation of how much memory in bytes the model requires.
|-
||EventsDatatable (Datatable)
||Returns the Datatable the model uses as a datasource for events. Returns ''null'' if the events Datatable is not defined or if model uses other than the Datatable datasource.
|-
||EventAttributes (AttributeType*)
||[[#AttributeType|EventAttributes]] in the model returned in the alphabetical order. Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
|-
||EventLog (EventLog)
||EventLog containing the entire model (i.e. event log where no filters have been applied). Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
|-
||Filters (Filter*)
||Returns an array of all [[#Filter|filters]] in the model.
|-
||Id (Integer)
||Model Id. Model Id is generated by QPR ProcessAnalyzer when the model is created.
|-
||LastModifiedBy (User)
||User who last time modified the model properties. Note that datatables containing the eventlog data are separate objects having similar fields to track the last modification and last data import.
|-
||LastModifiedDate (DateTime)
||Timestamp when the model was modified the last time.
|-
||Name (String)
||Model name.
|-
||NCache (Integer)
||Number of objects related to the model when the model is loaded into the memory.
|-
||NCaseAttributes (Integer)
||Number of [[#AttributeType|CaseAttributes]] in model. Works only for in-memory models.
|-
||NCases (Integer)
||Number of [[#Case|Cases]] in the model. Works only for in-memory models.
|-
||NEventAttributes (Integer)
||Number of [[#AttributeType|EventAttributes]] in model. Works only for in-memory models.
|-
||NEvents (Integer)
||Number of [[#Event|Events]] in model. Works only for in-memory models.
|-
||NEventTypes (Integer)
||Number of [[#EventType|EventTypes]] in the model. Works only for in-memory models.
|-
||Project (Project)
||[[#Project|Project]] where the model belongs to.
|-
||ProjectId (Integer)
||[[#Project|Project]] id where the model belongs to.
|-
||Status (String)
||
||
* Column names (String*)
Memory availability status of the model. There are the following statuses:
||
* '''Loading''': The model is currently loading into the memory. When the loading is ready, the status changes to ''online''. If the loading fails, the status changes to ''offline''.
Convert column names into column indexes. The indexes are starting from zero. If a column is not found, an exception is given.
* '''Offline''': The model is currently not loaded into the memory. The model needs to be loaded into the memory, so that analyses can be calculated from the model (occurs automatically when an analysis is requested).
* '''Online''': The model is in the memory and ready for analysis calculation. If the model is dropped from the memory, its status changes to ''offline''.
|-
||UsedDatatables
||Returns all datatables the model uses as a datasource.


Examples:
Example: List datatables used by a model:
<pre>
<pre>
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]).ColumnIndexes(["right", "id"])
StringJoin(", ", OrderByValue(ModelById(1).UsedDataTables.Name))
Returns: [1, 0]
</pre>
</pre>
|}
{| class="wikitable"
!'''Model functions'''
!'''Parameters'''
! '''Description'''
|-
|-
||Column (Object*)
||CalendarByName (BusinessCalendar)
||
||
* Column name
name (String)
||
||
Returns an array of values of given column in the order rows are in the datatable.
Returns a [[Business_Calendar|business calendar]] stored to the Model by the name of the calendar. Business calendars can be stored to models in the model properties. Returns ''null'', if a calendar with the provided name is not stored to the model.


Examples:
Examples:
<pre>
<pre>
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]).Column("right")
ModelById(123).CalendarByName("MyCalendar")
Returns: [zero, two, three]
</pre>
</pre>
|-
|-
||Columns (DataFrame)
||CreateDiagram (Diagram)
||Parameters dictionary
||
||
* Column indexes
Creates a [[Diagram_in_Expression_Language|diagram]] to the model. Parameters is a dictionary containing diagram properties. Following properties are available:
||
* '''Name''' (string): Diagram name that distinguishes diagrams in a model.
Creates a new DataFrame having only the defined columns of the original DataFrame.
* '''Description''' (string): Diagram description text.
* '''Content''' (dictionary): Diagram content as dictionary.


Note that Columns function is different than Columns property - difference is that the function has parameters.
Example:
<pre>
ModelById(1)
  .CreateDiagram(#{
    "Name": "My diagram",
    "Description": "This is my new diagram",
    "Content": #{ ... },
  })
</pre>
|-
||CreateFilter (Filter)
||Parameters dictionary
||Creates a filter to a model. Requires ''GenericWrite'' permission for the project and global ''CreateModel'' permission. If a filter with that name already exists in the model, an exception is thrown.
The parameters dictionary may have the following properties:
* '''Name''': Name of the filter. This property is mandatory.
* '''Description''': Description of the filter. This property is optional.
* '''Rules''': Filter rules for the filter defined as a dictionary according to the [[Filtering_in_QPR_ProcessAnalyzer_Queries|filter json format]]. This property is mandatory.
* '''PublishMode''': Publish mode of the filter which is one of the following: ''Private'', ''Public'' or ''Default''. This property is optional, and the default value is ''Private''.


Examples:
Example:
<pre>
<pre>
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]).Columns([1]).ToCsv()
let newFilter = modelById(1).CreateFilter(#{   
Returns string:
  "Name": "My Filter",
right
  "Rules": #{
zero
    "Items": [#{
two
      "Type": "IncludeCases",
three
      "Items": [#{
 
        "Type": "CaseAttributeValue",
        "Attribute": "Account Manager",
        "StringifiedValues": [ "0Robert Miller" ]
      }]
    }]
  },
  "PublishMode": "Public"
});
}
</pre>
</pre>
|-
|-
||Head
||DeletePermanently
||(none)
||Deletes the Model permanently. The model doesn't need to be in the recycle bin to be able to delete it permanently.
|-
||Modify (Model)
||Dictionary
||
||
* Number of rows
Modifies model properties. The parameter is a dictionary containing the properties to be changed. Following properties can be changed: ''Name'', ''Description'', ''ProjectId'', and ''Configuration''.
||
 
Creates a new DataFrame that only contains top number of rows of this DataFrame. If the DataFrame has less than n rows, all its rows are returned.
The function returns the updated model object. Requires the ''GenericWrite'' permission for the project and the global ''CreateModel'' permission.


Examples:
Example:
<pre>
<pre>
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]).Head(2).ToCsv()
ModelById(1)
Results string:
.Modify(#{
id;right
"Name": "My model",
0;zero
"Description": "My description",
2;two
"ProjectId": 2,
"Configuration": #{
"DataSource": #{
"Cases": #{
"DataSourceType": "datatable",
"DataTableName": "My cases datatable",
"Columns": #{
"CaseId": "Case Name"
}
},
"Events": #{
"DataSourceType": "datatable",
"DataTableName": "My events datatable",
"Columns": #{
"CaseId": "Case Name",
"EventType": "Event Type",
"Timestamp": "Start Time"
}
}
}
}
}
)
</pre>
</pre>
|-
|-
||Join
||ResetPreprocessings
||(none)
||
||
||
Removes all cached items related to the Model, e.g. preprocessings and calculation results. In practice, the Model is reset to a state where it was right after the model was loaded into memory.
1. Performs Join operation between two DataFrames by given columns.
|-
2. Parameters:
||Restore
2.1. other: Other DataFrame to join this DataFrame with.
||(none)
2.2. keys: Identifies columns to be matched with each other. Value can be either:
||Restores the Model from the recycle bin back to the original location.
2.2.1. Single column index (numerical) or name.
|-
2.2.1.1. In this case, the column identified with this value is used in both the DataFrames to match the rows.
||<span id="ToSqlDataFrame">ToSqlDataFrame</span>
2.2.2. An array of any number of:
||In-memory dataframe
2.2.2.1. Column identifiers or names
||Converts an in-memory dataframe to an SQL dataframe. In practice, an SQL query is created from the in-memory dataframe and the query is executed in the datasource so that the data is available in the datasource for further SQL operations. This function is intended only to small amounts of data which is less than 16384 rows.
2.2.2.1.1. In this case, the column identified with this value is used in both the DataFrames to match the rows.
2.2.2.2. Hierarchical arrays (#29290#) containing identifiers or names both in their context object and value object.
2.2.2.1.1. In this case, the column identified with the context value is used to identify the column in this DataFrame, whereas the column identified with the value of the context is used to identify the column in the other DataFrame to match the rows.
2.3. joinType: Optional parameter defining the join type. Supported values are:
2.3.1. "Inner":
2.3.1.1. Inner join (row is generated only if both context and other have the key value.
2.3.1.2. This is the default value.
2.3.2. "LeftOuter":
2.3.1.1. Left outer join (at least one row is generated for each context row, even if there is no matching other row, in which case null is given as value for the other columns)
3. Returns a new DataFrame that the result of the join operation. Note that the key-column of the other -DataFrame is not included.


Examples:
Example: Select matching cases from events data using in-memory dataframe:
Let("left", ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]));
<pre>
Let("right", ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]));
let model = ModelById(1);
left.join(right, "id", "inner").ToCsv()
let dfEvents = model.EventsDatatable.SqlDataFrame;
Returns string:
let inMemoryDf = ToDataFrame(
id;left;right
  [["1"], ["2"], ["3"]],
0;zero;zero
  [#{"Name": "id", "DataType": "String"}]
);
model.ToSqlDataFrame(inMemoryDf)
  .Join(dfEvents, ["id": "CaseId"])
  .SelectDistinct(["CaseId"])
  .Collect();
</pre>
|-
||<span id="TriggerNotifications">TriggerNotifications</span> (Boolean)
||Notification names (String*)
||Triggers the given notifications for the Model. Notifications are given by their names. Triggering means that the configured rules are run and notification emails are sent as defined by the rules. If the notification names parameter is not provided, all notifications in the Model are triggered.


Let("left", ToDataFrame([[0, "zero"], [1, "one"]], ["id", "left"]));
The function return ''true'' if any notification were triggered, otherwise ''false''.
Let("right", ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]));
<pre>
left.join(right, "id", "leftouter").ToCsv()
ModelById(123).TriggerNotifications(["Notification 1", "Notification 2"]);
Returns string:
Triggers notifications Notification 1 and Notification 2 in model id 123.
id;left;right
0;zero;zero
1;one;


Let("left", ToDataFrame([[0, 0, "zerozeroleft"], [0, 1, "zeroleft"], [1, 2, "oneleft"]], ["idleft1", "idleft2", "left"]));
ModelById(123).TriggerNotifications();
Let("right", ToDataFrame([[0, 0, "zerozeroright"], [0, 1, "zeroright"], [2, 3, "tworight"], [3, 4, "threeright"]], ["idright1", "idright2", "right"]));
Triggers all notifications in model id 123.
left.join(right, ["idleft1": "idright1"], "inner");
</pre>
|}


Returns string:
Function to get Model by model id:
idleft1;idleft2;left;idright2;right
{| class="wikitable"
0;0;zerozeroleft;0;zerozeroright
!'''Functions'''
0;0;zerozeroleft;1;zeroright
!'''Parameters'''
0;1;zeroleft;0;zerozeroright
! '''Description'''
0;1;zeroleft;1;zeroright
 
Let(""left"", ToDataFrame([[0, 0, ""zerozeroleft""], [0, 1, ""zeroleft""], [1, 2, ""oneleft""]], [""idleft1"", ""idleft2"", ""left""]));
Let(""right"", ToDataFrame([[0, 0, ""zerozeroright""], [0, 1, ""zeroright""], [2, 3, ""tworight""], [3, 4, ""threeright""]], [""idright1"", ""idright2"", ""right""]));
left.join(right, [""idleft1"": ""idright1"", ""idleft2"": ""idright2""], ""inner"");
Returns string:
idleft1;idleft2;left;right
0;0;zerozeroleft;zerozeroright
0;1;zeroleft;zeroright
|-
|-
||GroupBy
||ModelById
||
||
* Model id (Integer)
||
||
1. Create a new DataFrame based on the current DataFrame having rows grouped by given columns and values aggregated using given functions.
Returns [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#Model|Model]] object corresponding to the provided model id.
2. Parameters:
|}
2.1. columns: Columns to group the rows by identified by column indexes or column labels.
2.2. aggregationExpressions: A hierarchical array (#29290#) containing the name of the column as context object and the value as context value. The value can be either:
2.2.1. Expression object (#29355#), in which case the expression is evaluated in the context of each row in the DataFrame.
2.2.2. Otherwise the value is expected to be a constant value that is assigned as the value for all the rows.
3. Returns a new DataFrame whose rows are grouped by given keys and for group, given expressions are calculated separately. One row in the end result correspond with one group.


Examples:
==Object-centric model==
ToDataFrame([[0, "zero"], [0, "zero2"], [2, "two"], [2, "two"], [2, "two3"], [3, "three"]], ["id", "text"]).GroupBy(["id"],
Object-centric models additionally have the following properties and functions.
[
  "ids": Def("", Sum(id)),
  "texts": Def("", StringJoin(",", text)),
  "constant": 123
]).ToCsv()
Returns string:
ids;texts;constant
0;zero,zero2;123
6;two,two,two3;123
3;three;123


ToDataFrame([[0, "zero"], [0, "zero2"], [2, "two"], [2, "two"], [2, "two3"], [3, "three"]], ["id", "text"]).GroupBy(["id", "text"],
{| class="wikitable"
[
!'''Object-Centric model properties'''
  "ids": Def("", Sum(id)),
! '''Description'''
  "texts": Def("", StringJoin(",", text)),
|-
  "constant": 123
||IsOcelModel (boolean)
]).ToCsv()
||Returns ''true'' when the model is an OCEL model.
Returns string:
|-
ids;texts;constant
||OcelEvents (Datatable)
0;zero;123
||Datatable containing event data for the OCEL model. Value ''null'' is returned if event datatable has not been configured for the model. Throws an unsupported operation exception if the model is not an OCEL model.
0;zero2;123
|-
4;two,two;123
||OcelEventToObject (Datatable)
2;two3;123
||Datatable containing event-to-object relations data for the OCEL model. Value ''null'' is returned if event-to-object relation datatable has not been configured for the model. Throws an unsupported operation exception if the model is not an OCEL model.
3;three;123
|-
||OcelEventTypes (Dictionary)
||Returns a dictionary containing event type names as keys and the datatables holding event data for that event type in this OCEL model as value. An empty array is returned if event types datatable has not been configured for the model. Throws an unsupported operation exception if the model is not an OCEL model.


Analysis("OperationLog")
Example: Get datatable for "Create order" events:
.GroupBy(
<pre>
  ["User Name"],
ModelById(1).OcelEventTypes.Get("Create order")
  [
</pre>
    "User Name": Def("", Column("User Name")[0]),
|-
    "Count": Def("Rows", CountTop(Rows)),
||OcelObjects (Datatable)
    "Avg. Duration": Def("", Average(Duration)),
||Datatable containing objects data for the OCEL model. Value ''null'' is returned if object datatable has not been configured for the model. Throws an unsupported operation exception if the model is not an OCEL model.
    "Max. Duration": Def("", Max(Duration))
|-
  ]
||OcelObjectToObject (Datatable)
).ToCsv()
||Datatable containing object-to-object relations data for the OCEL model. Value ''null'' is returned if object-to-object relation datatable has not been configured for the model. Throws an unsupported operation exception if the model is not an OCEL model.
|-
||OcelObjectTypes (Dictionary)
||Returns a dictionary containing object type names as keys and the datatables holding data for that object type in this OCEL model as value. An empty array is returned if object types have not been configured for this model. Throws an unsupported operation exception if the model is not an OCEL model.
|}


Returns string (similar to this):
{| class="wikitable"
User Name;Count;Avg. Duration;Max. Duration
!'''Object-centric model functions'''
;207;0.617434782608696;20.556
!'''Parameters'''
Administrator;665;16.3750631578947;4225.497
! '''Description'''
qpr;128;2.158765625;20.346
 
Analysis("OperationLog").OrderBy(Duration).Head(1)
Results string:
id;right
0;zero
2;two
3;three
|-
|-
||Merge
||OcelEventType
||Event type name (String)
||
||
||
Datatable containing event type attributes of given event type in this OCEL model. Value ''null'' is returned if a datatable is not configured for this model for given event type. Throws an unsupported operation exception if the model is not an OCEL model.
1. Create a new DataFrame that has the contents of given (target) DataFrame merged with another (source) DataFrame using method described in parameters.
1.1. Principles somewhat follow how MERGE SQL command works in SQL Server.
1.2. In order for the merge to work properly, both the source and target table should have exactly the same columns in exactly the same order.
2. Parameters:
2.1. source: DataFrame to be merged with this (target) DataFrame.
2.2. matchColumns: Identifies columns to be matched with each other. Value can be either:
2.2.1. Single column index (numerical) or name.
2.2.1.1. In this case, the column identified with this value is used in both the DataFrames to match the rows.
2.2.2. An array of any number of:
2.2.2.1. Column identifiers or names
2.2.2.1.1. In this case, the column identified with this value is used in both the DataFrames to match the rows.
2.2.2.2. Hierarchical arrays (#29290#) containing identifiers or names both in their context object and value object.
2.2.2.1.1. In this case, the column identified with the context value is used to identify the column in this DataFrame, whereas the column identified with the value of the context is used to identify the column in the other DataFrame to match the rows.
2.3. copyOnMatch: An array similar to the one in 2.2.2 of column names or indexes of columns to copy from the source to target, if a matching row is found.
2.3.1. If the value is _remove (#27680#), then all the rows for which a match was found will be removed from the resulting table.
2.3.2. If the value is null (#27679#), then the target row will be completely replaced with all the columns in the source row that have identical name in both source and target table.
2.3.3. The default value is null.
2.3. copyIfNotMatchedInTarget: An array similar to the one in 2.3 of column names or indexes of columns to copy from the source to target, if a matching row is not found.
2.4. addIfNotMatchedInTarget: A boolean value that defines whether the source row is added to the result if there is no matching row in the target DataFrame.
2.4.1. The default value is true.
2.5. keepIfNotMatchedBySource: A boolean value that defines whether the target row is added to the result if there is no matching row in the source DataFrame.
2.5.1. The default value is true.
3. Returns a new DataFrame that has the combination of rows from both the DataFrames.
3.1. If the number of columns is different between this DataFrame and the other DataFrame, an exception is thrown.


Examples:
Example: Get datatable for "Create order" events:
Let("target", ToDataFrame([[0, "zero", "target"], [1, "", "target"]], ["id", "text", "frame"]));
<pre>
Let("source", ToDataFrame([[1, "one", "source"], [2, "two", "source"], [3, "three", "source"]], ["id", "text", "frame"]));
ModelById(1).OcelEventType("Create order")
</pre>
|-
||OcelObjectType
||Object type name (String)
||Datatable containing object type attributes of given object type in this OCEL model. Value ''null'' is returned if a datatable is not configured for this model for given object type. Throws an unsupported operation exception if the model is not an OCEL model.
|}


target.Merge(source, "id").ToCsv()
== Project ==
{| class="wikitable"
!'''Project properties'''
! '''Description'''
|-
||CreatedBy (User)
||User who created the Project.
|-
||CreatedDate (DateTime)
||Timestamp when the Project was created.
|-
||Configuration (Dictionary)
||Project settings as Dictionary object.
|-
||ConfigurationJson (String)
||Project settings as json string.
<pre>
{
  "ConnectionStringKeys": {
    "Snowflake": "MyKey1",
    "SqlServer": "MyKey2"
  }
}
</pre>
|-
||Dashboards (Dashboard*)
||Returns all [[Dashboard_in_Expression_Language|dashboards]] in the project.
|-
||Datatables (Datatable*)
||Returns all Datatables in the project.
|-
||DeletedDate (DateTime)
||Timestamp when the Project was deleted (moved to the recycle bin).
|-
||Description (String)
||Project description. The project description may contain line breaks.
|-
||DeletedBy (User)
||User who deleted the Project (moved to the recycle bin).
|-
||Id (Integer)
||Id of the Project.
|-
||LastModifiedBy (User)
||User who last modified the Project.
|-
||LastModifiedDate (DateTime)
||Timestamp when the Project was last modified (refers to the project name, description and parent, not the contents of the project).
|-
||Name (String)
||Name of the Project.
|-
||Models (Model*)
||Models that are in the Project.
|-
||Parent (Project)
||Parent project, i.e. a Project where the Project is located in the hierarchy of Projects. Returns ''null'' for root level Projects. Throws an error if user doesn't have access to the parent project.
|-
||ParentProjectId (Integer)
||Parent project id. Returns ''null'' for root level Projects. The parent project id is returned even if user doesn't have access to the parent project.
|-
||Scripts (Script*)
||Scripts that are in the Project.
|-
||Secrets (Dictionary*)
||Returns array of all [[Storing_Secrets_for_Scripts|secrets]] in the project as Dictionary with following properties:
* '''Name''' (string): Name of the secret.
* '''Type''' (string): Type of the secret which is one of the following: "odbc", "sap", "salesforce".
|}


Returns string (one key, default parameters, identical dataframe columns):
{| class="wikitable"
id;text;frame
!'''Project functions'''
0;zero;target
!'''Parameters'''
1;one;source
! '''Description'''
2;two;source
3;three;source
 
target.Merge(source, "id", ["text"]).ToCsv()
 
Returns string (one key, default parameters, identical dataframe columns, copy only text column from source):
id;text;frame
0;zero;target
1;one;target
2;two;
3;three;
 
target.Merge(source, "id", ["text"], _remove).ToCsv()
 
Returns string (one key, default parameters, identical dataframe columns, copy only text column from source, remove rows found only in source):
id;text;frame
0;zero;target
1;one;target
 
target.Merge(source, "id", ["text"], _remove, false).ToCsv()
 
Returns string (one key, identical dataframe columns, copy only text column from source, remove rows found only in source or only in target):
id;text;frame
1;one;target
 
target.Merge(source, "id", _remove, _remove, false).ToCsv()
 
Returns string (one key, identical dataframe columns, remove all rows):
id;text;frame
 
Let("target", ToDataFrame([[0, 0, "zerozeroleft", "target"], [0, 1, "zeroleft", "target"], [1, 2, "left", "target"], [4, 5, "fourleft", "target"]], ["idleft1", "idleft2", "textleft", "frame"]));
Let("source", ToDataFrame([[0, 0, "zerozeroright", "source"], [0, 1, "zeroright", "target"], [1, 2, "oneright", "source"], [2, 3, "tworight", "source"], [3, 4, "threeright", "source"]], ["idright1", "idright2", "textright", "frame"]));
target.Merge(source, ["idleft1": "idright1"]).ToCsv()
 
Returns string (one key, default parameters, different dataframe columns, copy all matching columns):
idleft1;idleft2;textleft;frame
0;0;zerozeroleft;source
0;0;zerozeroleft;target
0;1;zeroleft;source
0;1;zeroleft;target
1;2;left;source
4;5;fourleft;target
2;;;source
3;;;source
 
target.Merge(source, ["idleft1": "idright1"], []).ToCsv()
 
Returns string (one key, default parameters, different dataframe columns, copy only key column):
idleft1;idleft2;textleft;frame
0;0;zerozeroleft;target
0;0;zerozeroleft;target
0;1;zeroleft;target
0;1;zeroleft;target
1;2;left;target
4;5;fourleft;target
2;;;
3;;;
 
target.Merge(source, ["idleft1": "idright1", "idleft2": "idright2"]).ToCsv()
 
Returns string (two keys, default parameters, different dataframe columns, copy all matching columns):
idleft1;idleft2;textleft;frame
0;0;zerozeroleft;source
0;1;zeroleft;target
1;2;left;source
4;5;fourleft;target
2;3;;source
3;4;;source
 
target.Merge(source, ["idleft1": "idright1", "idleft2": "idright2"], ["textleft": "textright"]).ToCsv()
 
Returns string (two keys, default parameters, different dataframe columns, copy only textright -column):
idleft1;idleft2;textleft;frame
0;0;zerozeroright;target
0;1;zeroright;target
1;2;oneright;target
4;5;fourleft;target
2;3;tworight;
3;4;threeright;
 
target.Merge(source, ["idleft1": "idright1", "idleft2": "idright2"], ["textleft": "textright", "frame"]).ToCsv()
 
Returns string (two keys, default parameters, different dataframe columns, copy textright and frame -columns):
idleft1;idleft2;textleft;frame
0;0;zerozeroright;source
0;1;zeroright;target
1;2;oneright;source
4;5;fourleft;target
2;3;tworight;source
3;4;threeright;source
 
target.Merge(source, ["idleft1": "idright1", "idleft2": "idright2"], _remove).ToCsv()
 
Returns string (two keys, default parameters, different dataframe columns, remove all matching rows, copy only matching columns):
idleft1;idleft2;textleft;frame
4;5;fourleft;target
2;3;;
3;4;;
 
target.Merge(source, ["idleft1": "idright1", "idleft2": "idright2"], ["textleft": "textright"], ["idleft1": "idright1", "idleft2": "idright2", "textleft": "textright"]).ToCsv()
 
Returns string (two keys, default parameters, different dataframe columns, copy only textright-column for matching columns, copy id columns and textright-column for rows not found in target):
idleft1;idleft2;textleft;frame
0;0;zerozeroright;target
0;1;zeroright;target
1;2;oneright;target
4;5;fourleft;target
2;3;tworight;
3;4;threeright;
 
target.Merge(source, ["idleft1": "idright1", "idleft2": "idright2"], ["textleft": "textright"], ["idleft1": "idright1", "idleft2": "idright2", "textleft": "textright", "frame"]).ToCsv()
 
Returns string (two keys, default parameters, different dataframe columns, copy only textright-column for matching columns, copy id, frame and textright-column for rows not found in target):
idleft1;idleft2;textleft;frame
0;0;zerozeroright;target
0;1;zeroright;target
1;2;oneright;target
4;5;fourleft;target
2;3;tworight;source
3;4;threeright;source
 
target.Merge(source, ["idleft1": "idright1", "idleft2": "idright2"], null, ["idleft1": "idright1", "idleft2": "idright2", "textleft": "textright"]).ToCsv()
 
Returns string (two keys, default parameters, different dataframe columns, don't copy any columns from source for matching columns, copy id columns and textright-column for rows not found in target):
idleft1;idleft2;textleft;frame
0;0;zerozeroleft;source
0;1;zeroleft;target
1;2;left;source
4;5;fourleft;target
2;3;tworight;
3;4;threeright;
 
target.Merge(source, ["idleft1": "idright1", "idleft2": "idright2"], ["textleft": "textright"], ["idleft1": "idright1", "idleft2": "idright2", "textleft": "textright", "frame"], false).ToCsv()
 
Returns string (two keys, default parameters, different dataframe columns, copy only textright-column for matching columns, copy id, frame and textright-column for rows not found in target, remove all rows not found in source):
idleft1;idleft2;textleft;frame
0;0;zerozeroright;target
0;1;zeroright;target
1;2;oneright;target
2;3;tworight;source
3;4;threeright;source
|-
|-
||OrderBy (DataFrame)
||CreateDashboard (Dashboard)
||
||Parameters dictionary
* ordering expression
||Creates a dashboard to the project. ''EditDashboards'' permission to the project is required. The parameter is dictionary with following supported dashboard properties:
||
* '''Name''' (String): Name of the dashboard.
Creates a new DataFrame having rows ordered in an ascending order using the given expression evaluated on each row.
* '''Identifier''' (String): Identifier of the dashboard.
* '''Description''' (String): Description of the dashboard.
* '''Content''' (Dictionary): Content of the dashboard.


Examples:
Example: Create empty dashboard.
<pre>
<pre>
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "text"]).OrderBy(text).ToCsv()
ProjectById(1)
Returns string:
  .CreateDashboard(#{
id;right
    "Name": "My dashboard",
3;three
    "Identifier": "MyDashboard"
2;two
  });
0;zero
</pre>


Analysis("OperationLog").OrderBy(Duration).Head(1)
Example: Create dashboard with a chart.
Results string:
<pre>
id;right
ProjectById(1)
0;zero
  .CreateDashboard(#{
2;two
    "Name": "My dashboard",
3;three
    "Content": #{
      "version": 4,
      "typeName": "View",
      "name": "My dashboard",
      "subElements": [
        #{
          "position": #{
            "x": 0,
            "y": 0,
            "width": 0.5,
            "height": 0.5,
            "zOrder": 0
          },
          "element": #{
            "typeName": "Chart",
            "configuration": #{
              "root": #{
                "expressionType": "Cases",
                "expressionParameters": #{}
              },
              "measures": [#{
                "expressionType": "Count",
                "expressionParameters": #{}
              }]
            }
          }
        }
      ]
    }
  });
</pre>
</pre>
|-
|-
||OrderByDescending (DataFrame)
||<span id="CreateDatatable">CreateDatatable</span> (Datatable)
||
||
* ordering expression
* Parameters dictionary
||
||Creates datatable to the project. After creation, there are no columns or rows in the datatable. The function returns the created datatable entity. Following properties can be set for the datatable:
Creates a new DataFrame having rows ordered in an descending order using the given expression evaluated on each row.
* '''Name''' (string): Name of the datatable. This parameter is mandatory.
* '''Description''' (string): Description for the datatable. This parameter is optional.
* '''NameInDataSource''' (string): Table name in the datasource (e.g., in Snowflake) which this datatable is linked to. This parameter is optional.
* '''SchemaNameInDataSource''' (string): Schema name in the datasource (e.g., in Snowflake) which this datatable is linked to. This parameter is optional.
* '''DatabaseNameInDataSource''' (string): Database name in the datasource (e.g., in Snowflake) which this datatable is linked to. This parameter is optional.
* '''Type''' (string): Defines where the data for the datatable is located. Available values are '''Snowflake''', '''SqlServer''', and '''Local'''.  This parameter is optional and default value is defined by the [[PA_Configuration_database_table#General_Settings|DefaultDataSource]] setting.
* '''Connection''': Connection object for the datatable. This parameter is optional.


|-
Example: Create a new datatable:
||Persist (DataTable)
<pre>
||
ProjectById(1).CreateDatatable(#{
* DataTable name
  "Name": "My datatable"
* Additional parameters
});
||
</pre>
Writes the DataFrame into QPR ProcessAnalyzer database as a DataTable having the given name. If a DataTable with that name does not exist in the project, a new DataTable is created. If a DataTable with that name already exists, the DataFrame will be stored into that DataTable. The function returns a DataTable object which the data was persisted into.


The additional parameters support:
Example: Create Snowflake datatable linked to a custom table:
* Append: Can be used to determine whether to append (true) or overwrite (false) the existing data. Default is overwrite.
<pre>
* ProjectName: Name of the project under which the DataTable is to be created.
ProjectById(1).CreateDatatable(#{
* ProjectId: Id of the project under which the DataTable is to be created.
  "Name": "My datatable",
  "Description": "My description",
  "NameInDataSource": "MyTable",
  "SchemaNameInDataSource": "MySchema",
  "DatabaseNameInDataSource": "MyDatabase",
  "Type": "Snowflake"
});
</pre>


Examples:
Example: Create Snowflake datatable where connection string is stored as a [[Storing_Secrets_for_Scripts|secret]]:
<pre>
<pre>
Let("right", ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]));
ProjectById(1).CreateDatatable(#{
right.Persist("RightDataTable", ["ProjectName": "TestData"])
  "Name": "My datatable",
Results: Id of the new data table named "RightDataTable" created into project named TestData (which is created if it doesn't already exist). If the table already existed, its contents will be overwritten by the new content.
  "Type": "Snowflake",
 
  "Connection": ProjectById(1).CreateSnowflakeConnection(#{ "OdbcConnectionStringKey": "MyKey" })
Let("newData", ToDataFrame([[4, "four"]], ["id", "right"]));
});
newData.Persist("RightDataTable", ["ProjectName": "TestData", "Append": true])
Results: Id of the new data table named "RightDataTable" created into project named TestData (which is created if it doesn't already exist). If the table already existed, new content will be appended into the end of the table.
</pre>
</pre>
|-
|-
||SetColumns
||CreateModel (Model)
||
||
||
1. Create a new DataFrame having contents of this DataFrame and create a new columns and/or modify existing columns of the resulting DataFrame.
* Parameters dictionary
2. Parameters:
||Creates a model to a project. Requires ''GenericWrite'' permission for the Project and global ''CreateModel'' permission. If a model with that name already exists, an exception is thrown.
2.1. columnChanges: Name of the new column.
2.1.1. Hierarchical array (#29290#) containing the name of the column as context object and the value as context value. The value can be either:
2.1.1.1. Expression object (#29355#), in which case the expression is evaluated in the context of each row in the DataFrame.
2.1.1.2. Otherwise the value is expected to be a constant value that is assigned as the value for all the rows.
3. Returns a new DataFrame that is the copy of the original DataFrame with the following modifications:
3.1. For all the elements in the columnChanges -array:
3.1.1. If column name identifies a column that already exists, then the values in that column will be replaced with newly calculated values using the value.
3.1.2. The original column values can be used in the valueExpression, including those that were listed earlier in the list of column changes.


Examples:
Parameters dictionary has the following properties:
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "text"]).SetColumns(["both": Def("", text + "=" + id)]).ToCsv()
* '''Name''': Name of the model. This property is mandatory.
Result string:
* '''Description''': Description of the model. This property is optional.
id;text;both
* '''Configuration''': Configuration dictionary for the model. This property is technically optional, but a working model requires at least datasource settings to be defined.
0;zero;zero=0
2;two;two=2
3;three;three=3


ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "text"]).SetColumns(["text": Def("", text + "=" + id)]).ToCsv()
Example:
Result string:
<pre>
id;text
ProjectById(1).CreateModel(#{   
0;zero=0
  "Name": "My model",
2;two=2
  "Description": "My description",
3;three=3
  "Configuration": #{
 
    "DataSource": #{
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "text"]).SetColumns(["both": Def("", text + "=" + id), "both+1": Def("", both + 1), "text": Def("", "Done: " + Column("both+1")), "constant": 1234]).ToCsv()
      "Cases": #{
Result string:
        "DataSourceType": "datatable",
id;text;both;both+1;constant
        "DataTableName": "My cases datatable",
0;Done: zero=01;zero=0;zero=01;1234
        "Columns": #{
2;Done: two=21;two=2;two=21;1234
          "CaseId": "Case Name"
3;Done: three=31;three=3;three=31;1234
        }
      },
      "Events": #{
        "DataSourceType": "datatable",
        "DataTableName": "My events datatable",
        "Columns": #{
          "CaseId": "Case Name",
          "EventType": "Event Type",
          "Timestamp": "Start Time"
        }
      }
    }
  }
});
</pre>
|-
|-
||Tail (DataFrame)
||DatatableByName (Datatable)
||
||Datatable name (String)
* Number of rows
||
||
Creates a new DataFrame that has only the bottom number of rows of this DataFrame. If the DataFrame has less than n rows, all its rows are returned.
Returns Datatable by its name located in the project. Returns null, if Datatable with that name does not exist in the project.


Example:
Example:
<pre>
<pre>
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]).Tail(2).ToCsv()
ProjectById(123).DatatableByName("MyDatatable1")
Results string:
</pre>
id;right
 
2;two
Example: Get datatable by name, and create it if it doesn't exist:
3;three
<pre>
let project = ProjectById(123);
let datatableName = "MyDatatable1";
let datatable = project.DatatableByName(datatableName);
if (datatable == null) {
  datatable = project.CreateDatatable(datatableName);
}
</pre>
</pre>
|-
|-
||ToCsv (String)
||DeletePermanently
||(none)
||(none)
||Deletes the Project permanently. Note that the Project doesn't need to be in the recycle bin to be able to delete it permanently.
|-
||Restore
||(none)
||Restores the Project from the recycle bin back to the original location.
|-
||ModelByName (Model)
||Model name (String)
||
||
Converts a DataFrame into a CSV data. The CSV data has the following formatting:
Returns Model by its name located in the project. Returns null, if Model with that name does not exist in the project.
* Column separator: semicolon
* Decimal separator in numeric fields: period
* Quotation character for text fields: double quotes (used when the textual value contains semicolon, double quotes, linebreak or tabulator)
* Escape character: Double quotes in textual fields are escaped with two double quotes.
* Date format for date fields: yyyy-MM-dd HH:mm:ss,fff
* First line: contains column headers


Example:
Example:
<pre>
<pre>
ToDataFrame([[0, "zero"], [2, "two"], [3, "three"]], ["id", "right"]).ToCsv()
ProjectById(123).ModelByName("My Model 1")
Returns string:
id;right
0;zero
2;two
3;three
</pre>
</pre>
|-
|-
||Where (DataFrame)
||<span id="ModifyProject">Modify</span> (Project)
||Dictionary of settings to change
||
||
* condition expression
Change project settings. Following settings are supported:
||
* '''Name''' (String): Name of the project.
Creates a new DataFrame having only rows for which the given condition expression returns true. The condition expression can refer to the columns of the DataFrame (see the example below).
* '''Description''' (String): Description text of the project.
* '''ParentProjectId''' (Integer): Parent project id. Changing this effectively moves the project into different parent project.
* '''SnowflakeConnectionStringKey''' (String): Snowflake connection string key for the project. Snowflake datatables in the project will use connection string behind this key (unless specified by the datatatable).
* '''SqlServerConnectionStringKey''' (String): SQL Server connection string key. SQL Server datatables in the project will use connection string behind this key (unless specified by the datatatable).
 
''ManageProject'' permission is needed to set ''SnowflakeConnectionStringKey'' and ''SqlServerConnectionStringKey''.


Examples:
Example: Change project name and move project into other parent project:
<pre>
<pre>
Let("df", ToDataFrame([[0, "zero"], [2, "two", true], [3, "three"]], ["id", "string"]));
ProjectById(1)
  .Modify(#{
    "Name": "Project 1"
    "ParentProjectId": 2
});
</pre>


All the following expression return the same:
Example: Set Snowflake connection string key for the project:
df.Where(id < 3);
<pre>
df.Where(Column("id") < 3);
ProjectById(1)
df.Where(_[0] < 3);
  .Modify(#{
 
    "SnowflakeConnectionStringKey": "MyKey1"
Returns string:
});
id;string
0;zero
2;two
</pre>
</pre>
|-
|-
||Zip (DataFrame)
||ScriptByName (Script)
||Script name (String)
||
||
* DataFrame
Returns Script by its name located in the project. Returns null, if Script with that name does not exist in the project.
||
Creates a new DataFrame that has the contents of given DataFrame appended as new columns into the end of this DataFrame. Returns a new DataFrame that has the colums from both the data frames so that the columns from the other DataFrame are appended to the end of the columns of this DataFrame. If the number of rows is different between this DataFrame and the other DataFrame, an exception is thrown. There must not be duplicate column names in the DataFrames - otherwise an exception is thrown.


Examples:
Example:
<pre>
<pre>
Let("df1", ToDataFrame([[0, "zero"], [1, "one"], [4, "four"]], ["id", "text"]));
ProjectById(123).ScriptByName("MyScript1")
Let("df2", ToDataFrame([[1, "one"], [2, "two"], [3, "three"]], ["id2", "text2"]));
df1.Zip(df2).ToCsv();
Returns string:
id;text;id2;text2
0;zero;1;one
1;one;2;two
4;four;3;three
</pre>
</pre>
|-
|-
|}
||<span id="SetSecret">SetSecret</span>
||
# Secret type (string)
# Secret name (string)
# Secret value (string)
||Sets or adds a [[Storing_Secrets_for_Scripts|secret]] for the project. Setting the secret value to ''null'' removes the secret. There can be several secrets with the same name in the same project if the type of the secret is different. Setting secrets requires the project specific ''ManageProject'' permission.


== Datatable ==
Parameters:
Datatables are used to store data in QPR ProcessAnalyzer persistently. Datatables consist of one-to-many named columns and zero-to-many rows. The Datatable entity in the expression language is used to access Datatables metadata, such as name, description and audit information. The actual data contents can be accessed by using the DataFrame property, which causes the actual data to be loaded from the database into memory for calculations.
# '''Type''' (string): Secret type which is one of the following:
#* "externaldatatableconnection": ODBC connection string used by a datatable (e.g., connection string to Snowflake or SQL Server database)
#* "odbc": ODBC connection string (e.g., to extract data, or load a model directly)
#* "sap": SAP password
#* "salesforce": Salesforce password
# '''Name''' (string): Secret name, used to refer to the secret in the commands.
# '''Value''' (string): Secret value which contains the confidential information.


Datatables have many usecases:
Example: Set SAP password:
* [[#Project|project]] can load their data from Datatables.
<pre>
* Datatables can be used to store data extracted from external systems
ProjectById(1).SetSecret("sap", "MySapPassword", "I l0ve 5AP!");
* Intermediate ETL calculation results can be stored to Datatables
</pre>
* It's possible to visualize data from Datatables directly in the dashboards without building a QPR ProcessAnalyzer model


Datatables are stored to QPR ProcessAnalyzer database as database tables. Each datatable belongs to a [[#Project|project]].
Example: Remove SAP password:
<pre>
ProjectById(1).SetSecret("sap", "MySapPassword", null);
</pre>
|}


Function to get Project by id:
{| class="wikitable"
{| class="wikitable"
!'''Datatable properties'''
!'''Functions'''
!'''Parameters'''
! '''Description'''
! '''Description'''
|-
|-
||CreatedDate (DateTime)
||ProjectById
||Timestamp when the datatable was created.
||Project id (Integer)
||
Returns project object corresponding to the provided project id.
|-
|-
||DataFrame (DataFrame)
||ProjectByName
||Contents of the DataTable as a [[#DataFrame|DataFrame]].
||Project name (String)
|-
||
||Description (String)
Returns project object by given project name. If there is no such project or user doesn't have access to it, ''null'' value is returned. If there are multiple projects with the same name, one of them is returned.
||Datatable description. The datatable description may contain line breaks.
 
|-
Example:
||Id (Integer)
<pre>
||Datatable Id. Datatable Id is generated by QPR ProcessAnalyzer when the datatable is created.
let project = ProjectByName("My Project");
|-
</pre>
||LastImportDate (DateTime)
||Timestamp when data was the last time imported to the datatable.
|-
||LastModifiedDate (DateTime)
||Timestamp when the datatable was modified the last time.
|-
||Name (String)
||Name of the datatable.
|-
||NColumns (Integer)
||Number of columns in the datatable.
|-
||NRows (Integer)
||Number of data rows in the datatable.
|-
||Project (Project)
||[[#Project|Project]] the datatable belongs to.
|}
|}


== Model==
== Script ==
Scripts are entities that contain executable code, that can be run. Usually scripts contains ETL routines but also other kind of tasks are possible.


{| class="wikitable"
{| class="wikitable"
!'''Model properties'''
!'''Script properties'''
! '''Description'''
! '''Description'''
|-
|-
||CaseAttributes (AttributeType*)
||Code (String)
||[[#AttributeType|CaseAttributes]] in the model returned in the alphabetical order. Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
||Script code.
|-
|-
||ConfigurationJson (String)
||CreatedBy (User)
||Returns an model configuration in JSON format.
||User who created the Script.
|-
|-
||CreatedDate (DateTime)
||CreatedDate (DateTime)
||Timestamp when the model was created.
||Timestamp when the Script was created.
|-
|-
||DefaultFilterId (Integer)
||CurrentRunStart (DateTime)
||Default filter id of the model.
||Timestamp of the current run start. Null if the script is currently not running.
|-
||ConfigurationJson (String)
||Returns an model configuration in JSON format.
|-
|-
||Description (String)
||Description (String)
||Model description. The model description may contain line breaks.
||Description of the Script.
|-
|-
||EstimatedMemory (Integer)
||Id (Integer)
||Returns an estimation of how much memory in bytes the model requires.
||Id of the Script.
|-
|-
||EventAttributes (AttributeType*)
||Language (String)
||[[#AttributeType|EventAttributes]] in the model returned in the alphabetical order. Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
||Either of the following scripting language: '''Expression''' or '''SQL'''. When language is Expression, the script is run as an expression script, and when language is SQL, the script is run as an SQL script (using the sandbox database).
|-
|-
||EventLog (EventLog)
||LastModifiedBy (User)
||Model EventLog containing all events in the model, i.e. no filters have been applied. Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
||User who last modified the Script.
|-
||Id (Integer)
||Model Id. Model Id is generated by QPR ProcessAnalyzer when the model is created.
|-
||LastImportDate (DateTime)
||Timestamp when data was the last time imported to the model.
|-
|-
||LastModifiedDate (DateTime)
||LastModifiedDate (DateTime)
||Timestamp when the model was modified the last time.
||Timestamp when the Script was last modified.
|-
|-
||Name (String)
||LastRunEnd (DateTime)
||Model name.
||Timestamp of the last completed script run end (either successful completion or failure). Null if the Script hasn't been run yet.
|-
|-
||NBookmarks (Integer)
||LastRunResult (String)
||Number of bookmarks in the model.
||Result of the last run. Options are:
* '''Completed''': The last run was completed successfully.
* '''Failed''': An error occurred during the last run, so likely the script did not complete as intended.
* '''Aborted''': Script run was manually stopped prematurely by a user, so the script did not proceeded in the end.
 
Null if the Script hasn't been run yet.
|-
|-
||NCache (Integer)
||LastRunStart (DateTime)
||Amount of objects related to the model when the model is loaded into the memory.
||Timestamp of the last completed script run start time. Null if the Script hasn't been run yet.
|-
|-
||NCaseAttributes (Integer)
||Name (String)
||Number of [[#AttributeType|CaseAttributes]] in model.
||Name of the Script.
|-
||NCases (Integer)
||Number of [[#Case|Cases]] in the model.
|-
||NEventAttributes (Integer)
||Number of [[#AttributeType|EventAttributes]] in model.
|-
||NEvents (Integer)
||Number of [[#Event|Events]] in model.
|-
||NEventTypes (Integer)
||Number of [[#EventType|EventTypes]] in the model.
|-
|-
||NFilters (Integer)
||OperationId (Integer)
||Number of filters in the model.
||Id of the operation which runs the Script. Null if the script is currently not running.
|-
||NOpens (Integer)
||Number of times analysis has been requested from the model.
|-
|-
||Project (Project)
||Project (Project)
||[[#Project|Project]] the model belongs to.
||Project where the Script is located. Null if the script is in the global context.
|-
|-
||ProjectId (Integer)
||ProjectId (Integer)
||[[#Project|Project]] id the model belongs to.
||Id of the project where the Script is located. Null if the script is in the global context.
|-
|-
||Status (String)
||Status (String)
||
||Current status of the script. Options are:
Memory availability status of the model. There are the following statuses:
* '''Ready''': Script is not running. In this status, the script can be started (changing the status to ''Running'').
* '''Loading''': The model is currently loading into the memory. When the loading is ready, the status changes to ''online''. If the loading fails, the status changes to ''offline''.
* '''Running''': Script is running. In this status, the script can be stopped (changing the status to ''Stopping''). Calling stop just requests a script to stop, and the actual stopping occurs some time later.
* '''Offline''': The model is currently not loaded into the memory. The model needs to be loaded into the memory, so that analyses can be calculated from the model (occurs automatically when an analysis is requested).
* '''Stopping''': Script has been requested to be stopped, but it's still running. In this status, neither start nor stop can be called for the script. When the script eventually stops, its status changes to ''Ready''.
* '''Online''': The model is in the memory and ready for analysis calculation. If the model is dropped from the memory, its status changes to ''offline''.
|}
|}


Notes:
* Regarding properties returning object counts: For models downloaded from a [[QPR_ProcessAnalyzer_Model_Datasources|datatable, ODBC or expression datasource]], the numbers represent the situation in the previous data load. ''Null'' is returned for models downloaded from an ODBC datasource, if the model has never been loaded into the memory. Note also that if [[QPR_ProcessAnalyzer_Model_JSON_Settings#Case_Permissions|Case permissions]] are used for the model, ''null'' is returned for data security reasons.
* Using ''CaseAttributes'', ''EventAttributes'' or ''Eventlog'' properties of the Model object requires the model to be loaded into the memory. If the model is not in the memory, it's loaded when these properties is called. Other Model object properties down require the model to be in the memory.
== Project ==
{| class="wikitable"
{| class="wikitable"
!'''Project properties'''
!'''Script functions'''
!'''Parameters'''
! '''Description'''
! '''Description'''
|-
|-
||Id (Integer)
||Run (Object)
||Id of the Project. It's generated by QPR ProcessAnalyzer when the model is loaded.
||Dictionary of parameters
||
Runs the script using the provided parameters. The parameters are available in the script as variables (see the example). Any type of variables can be passed to the script. Note that if the script assumes certain variables, but that they are not passed to the script, the script run will throw an error.
 
For SQL scripts, the passed parameters are available in the script as variables in format '''@_parameter_<ParameterName>''' where <ParameterName> is the name of the parameter, e.g. ''parameter_myParameter1''. Only string type of parameters can be used, so any other type of data in parameter values is converted into strings.
 
The return value of the script is returned by the Run function. Expression scripts return a value with the ''return'' statement or alternatively the result of the last line of the script is the return value. If the script does not return any value, the Run function returns ''_empty''. For SQL scripts, the return value is the last dataset produced by the script (returned as a DataFrame) (SQL scripts might create several datasets using the ''--#ShowReport'' command or the ''Show'' parameter).
 
When a script is called using the Run function, the called script status does not change, because it's the parent script that is ''Running''. Also the called script log is not filled, but instead the logging goes to the calling script.
 
It's possible to call a script using the Run function several times simultaneously.
 
If there is an error when running the called script, the Run function throws the error to the calling script.
 
Scripts are run in the script entity context, so for example the following properties are available:
* Id: Script id
* Name: Script name
* Project.Id: Project id where the script is located
* Project.Name: Name of the project where the script is located
 
Example: Following script (id 123) raises a specified number to a specified power:
<pre>
return Pow(numberToRaise, exponent);
</pre>
 
The script can be called as follows (returning 16):
<pre>
let runResult = ScriptById(123).Run(#{
  "numberToRaise": 4,
  "exponent": 2
})
</pre>
|-
||<span id="Start">Start</span>
||Dictionary of parameters
||Starts the script. The function call doesn't wait for the script run to complete (i.e., asynchronous behavior) which is sane as starting the script in the [[Managing_Scripts#Starting_Script|Workspace]].
 
Parameters to the script can be provided as a dictionary of name-value pairs (which is not possible when script is started in the Workspace). Return value is the script run id (integer) if the script was started. If the script is already running, return value is ''null''.
 
Example: Start script (without parameters) and store the script run id:
<pre>
let runId = ScriptById(1).Start();
</pre>
 
Example: Start script with passing parameters:
<pre>
ScriptById(1).Start(#{
  "variable1": "val1",
  "variable2": 5
});
</pre>
|-
|-
||Name (String)
||Stop
||Name of the Project.
||
||Stops the script. The operation doesn't wait for the stopping to complete (i.e., asynchronous behavior) which is same as stopping the script in the [[Managing_Scripts#Stopping_Script|Workspace]]. Depending on the operation that the script is performing, the stopping might take some time.
 
Return value is the script run id (integer) if the script was running. If the script isn't running, the return value is ''null''.
 
Example:
<pre>
let runId = ScriptById(1).Stop();
</pre>
|}
 
Function to get a script by the script id:
{| class="wikitable"
!'''Functions'''
!'''Parameters'''
! '''Description'''
|-
|-
||Models (Model*)
||ScriptById
||Models that are in the Project.
||
* Script id (Integer)
||
Returns Script object corresponding to the given script id. If script with the given id doesn't exist or user doesn't have permissions to it, an error is given.
|}
|}


Line 745: Line 926:


{| class="wikitable"
{| class="wikitable"
!'''User properties'''
!'''User/group properties'''
! '''Description'''
! '''Description'''
|-
|-
Line 756: Line 937:
||FullName (String)
||FullName (String)
||Full name of the user or group name.
||Full name of the user or group name.
|-
||GlobalPermissions (String*)
||Array of global [[Roles and Permissions#Global_and_Project_Roles|permissions]] of the user. Global permissions come from the global roles assigned to the user and groups that the user belongs to. Note that to get the effective permissions for certain objects, also project specific permissions need to be taken into account.
|-
|-
||GroupMemberNames (String*)
||GroupMemberNames (String*)
Line 768: Line 952:
||Groups (User*)
||Groups (User*)
||Array of user groups the user belongs to. This property is available for users.
||Array of user groups the user belongs to. This property is available for users.
|-
||HasPassword (Boolean)
||Returns true if user has a password defined in QPR ProcessAnalyzer and thus user can authenticate using the password. If user doesn't have a password, the SAML authentication is the only way to log in. ''ManageUsers'' permission is needed to access this property for other users.
|-
|-
||Id (Integer)
||Id (Integer)
Line 778: Line 965:
||Returns true if the user is a user group.
||Returns true if the user is a user group.
|-
|-
||LogonMessage (String)
||IsLocked (Boolean)
||Returns the logon message of the user.
||Returns true if user account is currently [[User_Session_Management#Preventing_password_guessing_attacks|locked]]. ''ManageUsers'' permission is needed to access this property.
|-
||LastLockedDate (DateTime)
||Returns date when user account was locked the last time. Returns ''null'' if the user account has never been locked. ''ManageUsers'' permission is needed to access this property.
|-
||LastLoginDate (DateTime)
||Returns date when the user made last successful login. ''ManageUsers'' permission is needed to access this property for other users.
|-
|-
||Name (String)
||Name (String)
||Login name of the user or group.
||Login name of the user or group.
|-
|-
||Roles (String Array*)
||Roles (Object**)
||Roles of user as string array.
||
Returns all roles of the user (both global and project roles) as a nested array structure.
 
Example:
<pre>
ToJson(Users.Where(name == "qpr").Roles)
Returns (for example):
[
  [{"calcId": "Project:1"}, "Administrator"],
  [{"calcId": "Project:2"}, "Analyzer"],
  [{"calcId": "Project:3"}, "Viewer"],
  [null, "RunScripts"],
  [null, "Administrator"]
]
</pre>
|}
|}


{| class="wikitable"
!'''User/group functions'''
!'''Parameters'''
! '''Description'''
|-
||EffectivePermissionsFor (String Array)
||
* Project to get permissions
||
Returns effective (actual) permission of the user to the given project. Project is given as a [[#Project|project object]] (not as a project id). Effective permissions determine the actual permissions that the user has, i.e. a combination of all permissions assigned to the user and groups the user belong to, including both project specific and global roles.
Permissions for the EffectivePermissionsFor function are as follows:
* All users can query their own permissions
* To get permissions for any user, the user needs to have [[Roles_and_Permissions|ManageUsers permission]].
Note that ''inactive'' users don't have any effective permissions, so the EffectivePermissionsFor function does not return any permissions for those users.
Examples:
<pre>
EffectivePermissionsFor(ModelById(1234).Project)
Returns (for example): ["EditDashboards", "Filtering", "GenericRead"]
</pre>
|-
||GetAttribute
||
||
Returns user attribute value by given attribute name and optionally by the model/project/dashboard context. Supported data types are String, Integer, Float, and DateTime. To store more complex data types, data can be converted into json and stored as string. If the attribute doesn't exist, null is returned.
For example, if using dashboard as context, the attributes are effectively bound to each user and each dashboard separately. Thus, there can be several attributes with the same name as long as the dashboard is different.
Parameters:
# '''Attribute name''' (String): Name of the attribute.
# '''Attribute context''' (Project/Model/Dashboard): Optional context object the attribute is linked to.
Users have permissions to get attributes for themselves, and also (administrator) users with global ''ManageUsers'' permission can get attributes for all users. In addition, if using the context object, the ''GenericRead'' permission is required for the context object.
Example: Get user attribute MyDataValue for myself:
<pre>
CurrentUser
  .GetAttribute("MyDataValue");
</pre>
Example: Get user attribute MyDataValue for user John:
<pre>
Users
  .Where(Name=="John")
  .GetAttribute("MyDataValue");
</pre>
Example: Get user attribute MyDataValue for user 1 related to dashboard id 1:
<pre>
UserById(1)
  .GetAttribute("MyDataValue", DashboardById(1));
</pre>
|-
||SetAttribute
||
||
Sets user attribute value for given attribute name and optionally for the model/project/dashboard context. Supported data types are String, Integer, Float, and DateTime. To store more complex data types, data can be converted into json and stored as string. If setting value ''null'', the user attribute is removed. Required permissions are same as in the GetAttribute function.
Parameters:
# '''Attribute name''' (String): Name of the attribute.
# '''Attribute value''' (String/Integer/Float/DateTime): Attribute value to be stored.
# '''Attribute context''' (Project/Model/Dashboard): Optional context object the attribute value is linked to.


[[Category: QPR ProcessAnalyzer]]
Example: Set user attribute MyDataValue for myself:
<pre>
CurrentUser
  .SetAttribute("MyDataValue ", "value");
</pre>
 
Example: Set value 123 as user attribute MyDataValue for user John:
<pre>
Users
  .Where(Name=="John")
  .SetAttribute("MyDataValue", 123);
</pre>
 
Example: Set current time as user attribute MyDataValue for user 1 related to dashboard id 1:
<pre>
UserById(1)
  .GetAttribute("MyDataValue", Now, DashboardById(1));
</pre>
|}
 
Function to get User by user id:
{| class="wikitable"
!'''Functions'''
!'''Parameters'''
! '''Description'''
|-
||UserById
||
* User id (Integer)
||
Returns User object corresponding to the provided user id.
|}

Latest revision as of 13:59, 17 October 2024

Filter

Filters contain a set of filter rules used to filter cases and events in models. Filters are objects located in the models. Filters are owned by the creator user, and when a filter publish mode is private, only the creator can use it.

Filter properties Description
CreatedBy (User) Returns the user who created the filter.
CreatedDate (DateTime) Returns date when the filter created date.
Description (String) Returns description of the filter.
Id (Integer) Returns id of the filter.
LastModifiedBy (User) Returns user who modified the filter.
LastModifiedDate (DateTime) Returns date when the filter last modified.
Model Returns model where the filter belongs to.
ModelId (Integer) Returns model where the filter belongs to.
Name (String) Returns the name of the filter.
Project Returns project where the filter belongs to.
ProjectId (Integer) Returns project id where the filter belongs to.
PublishMode (String) Returns publish mode of the filter, one of the following: Private, Public, or Default.
Rules (Dictionary) Returns a dictionary containing the filter rules in the filter.
Filter functions Parameters Description
DeletePermanently (none)

Deletes the filter permanently. To delete own filters, the Filtering permission is needed, and to delete any filters the ManageViews permission is needed.

Modify Dictionary

Modifies filter properties. The parameter is a dictionary containing the properties to be changed. Following properties can be changed: Name, Description, PublishMode, and Rules.

The function returns the updated filter object. Requires GenericWrite permission for the Project and global CreateModel permission.

Example: 

FilterById(1)
	.Modify(#{
		"Name": "My filter",
		"Description": "My description",
		"PublishMode": "Public",
		"Rules": #{
			"Items": [
				#{
					"Type": "IncludeCases",
					"Items": [
						#{
							"Type": "CaseAttributeValue",
							"Attribute": "Account Manager",
							"StringifiedValues": [
								"0Mary Wilson"
							]
						}
					]
				}
			]
		}
	}
)

Function to get filter id:

Functions Parameters Description
FilterById
  • Filter id (Integer)

Returns Filter object corresponding to the provided filter id.

Model

Notes:

  • For in-memory models that are offline, the object counts represent the situation when the model was last time online (loaded into the memory). null is returned if the model has never been loaded into the memory.
  • If Case permissions are used for the model, and user doesn't have GenericWrite permission for the model, null is returned for data security reasons. Users that have the GenericWrite permission, see null when the model is offline, and when online, they see counts where the case level permissions settings are applied.
  • Properties CaseAttributes, EventAttributes and Eventlog work only for the in-memory models and they require the model to be loaded into the memory. If the model is not in the memory, it is loaded when these properties is used. Other model properties down require the model to be in the memory.
Model properties Description
Calendars (BusinessCalendar*)

Returns all business calendars stored to the Model as an array. Returns an empty array, if there are no business calendars stored to the model. Note: UI allows to set only one business calendar for a Model.

CaseAttributes (AttributeType*) CaseAttributes in the model returned in the alphabetical order. Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
CasesDatatable (Datatable) Returns the Datatable the model uses as a datasource for cases. Returns null if the cases Datatable is not defined or if model uses other than the Datatable datasource.
Configuration (Dictionary) Returns the Model configuration as dictionary. Example: 
ModelById(123).Configuration.DataSource.Events.DataTableName
ConfigurationJson (String) Returns the Model configuration as JSON string.
CreatedBy (User) User who created the model.
CreatedDate (DateTime) Timestamp when the model was created.
DefaultCalendar (BusinessCalendar) Returns the default business calendar of the Model. Returns null, if there are no calendars in the Model or no calendar has been set as a default calendar. Note: UI allows to set only one business calendar for a Model, which is also the default calendar.
DefaultFilter (Filter) Default filter of the model. Returns null if the model does not have a default filter.
DefaultFilterId (Integer) Default filter id of the model. Returns null if the model does not have a default filter.
Description (String) Model description. The model description may contain line breaks.
DeletedDate (DateTime) Timestamp when Model was deleted (moved to the recycle bin).
DeletedBy (User) User how deleted the Model.
Diagrams (Diagram*) Returns an array of all diagrams in the model.
EstimatedMemory (Integer) Returns an estimation of how much memory in bytes the model requires.
EventsDatatable (Datatable) Returns the Datatable the model uses as a datasource for events. Returns null if the events Datatable is not defined or if model uses other than the Datatable datasource.
EventAttributes (AttributeType*) EventAttributes in the model returned in the alphabetical order. Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
EventLog (EventLog) EventLog containing the entire model (i.e. event log where no filters have been applied). Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
Filters (Filter*) Returns an array of all filters in the model.
Id (Integer) Model Id. Model Id is generated by QPR ProcessAnalyzer when the model is created.
LastModifiedBy (User) User who last time modified the model properties. Note that datatables containing the eventlog data are separate objects having similar fields to track the last modification and last data import.
LastModifiedDate (DateTime) Timestamp when the model was modified the last time.
Name (String) Model name.
NCache (Integer) Number of objects related to the model when the model is loaded into the memory.
NCaseAttributes (Integer) Number of CaseAttributes in model. Works only for in-memory models.
NCases (Integer) Number of Cases in the model. Works only for in-memory models.
NEventAttributes (Integer) Number of EventAttributes in model. Works only for in-memory models.
NEvents (Integer) Number of Events in model. Works only for in-memory models.
NEventTypes (Integer) Number of EventTypes in the model. Works only for in-memory models.
Project (Project) Project where the model belongs to.
ProjectId (Integer) Project id where the model belongs to.
Status (String)

Memory availability status of the model. There are the following statuses:

  • Loading: The model is currently loading into the memory. When the loading is ready, the status changes to online. If the loading fails, the status changes to offline.
  • Offline: The model is currently not loaded into the memory. The model needs to be loaded into the memory, so that analyses can be calculated from the model (occurs automatically when an analysis is requested).
  • Online: The model is in the memory and ready for analysis calculation. If the model is dropped from the memory, its status changes to offline.
UsedDatatables Returns all datatables the model uses as a datasource.

Example: List datatables used by a model: 

StringJoin(", ", OrderByValue(ModelById(1).UsedDataTables.Name))
Model functions Parameters Description
CalendarByName (BusinessCalendar)

name (String)

Returns a business calendar stored to the Model by the name of the calendar. Business calendars can be stored to models in the model properties. Returns null, if a calendar with the provided name is not stored to the model.

Examples: 

ModelById(123).CalendarByName("MyCalendar")
CreateDiagram (Diagram) Parameters dictionary

Creates a diagram to the model. Parameters is a dictionary containing diagram properties. Following properties are available:

  • Name (string): Diagram name that distinguishes diagrams in a model.
  • Description (string): Diagram description text.
  • Content (dictionary): Diagram content as dictionary.

Example: 

ModelById(1)
  .CreateDiagram(#{
    "Name": "My diagram",
    "Description": "This is my new diagram",
    "Content": #{ ... },
  })
CreateFilter (Filter) Parameters dictionary Creates a filter to a model. Requires GenericWrite permission for the project and global CreateModel permission. If a filter with that name already exists in the model, an exception is thrown.

The parameters dictionary may have the following properties:

  • Name: Name of the filter. This property is mandatory.
  • Description: Description of the filter. This property is optional.
  • Rules: Filter rules for the filter defined as a dictionary according to the filter json format. This property is mandatory.
  • PublishMode: Publish mode of the filter which is one of the following: Private, Public or Default. This property is optional, and the default value is Private.

Example: 

let newFilter = modelById(1).CreateFilter(#{    
  "Name": "My Filter",
  "Rules": #{
    "Items": [#{
      "Type": "IncludeCases",
      "Items": [#{
        "Type": "CaseAttributeValue",
        "Attribute": "Account Manager",
        "StringifiedValues": [ "0Robert Miller" ]
      }]
    }]
  },
  "PublishMode": "Public"
});
}
DeletePermanently (none) Deletes the Model permanently. The model doesn't need to be in the recycle bin to be able to delete it permanently.
Modify (Model) Dictionary

Modifies model properties. The parameter is a dictionary containing the properties to be changed. Following properties can be changed: Name, Description, ProjectId, and Configuration.

The function returns the updated model object. Requires the GenericWrite permission for the project and the global CreateModel permission.

Example: 

ModelById(1)
	.Modify(#{
		"Name": "My model",
		"Description": "My description",
		"ProjectId": 2,
		"Configuration": #{
			"DataSource": #{
				"Cases": #{
					"DataSourceType": "datatable",
					"DataTableName": "My cases datatable",
					"Columns": #{
						"CaseId": "Case Name"
					}
				},
				"Events": #{
					"DataSourceType": "datatable",
					"DataTableName": "My events datatable",
					"Columns": #{
						"CaseId": "Case Name",
						"EventType": "Event Type",
						"Timestamp": "Start Time"
					}
				}
			}
		}
	}
)
ResetPreprocessings (none)

Removes all cached items related to the Model, e.g. preprocessings and calculation results. In practice, the Model is reset to a state where it was right after the model was loaded into memory.

Restore (none) Restores the Model from the recycle bin back to the original location.
ToSqlDataFrame In-memory dataframe Converts an in-memory dataframe to an SQL dataframe. In practice, an SQL query is created from the in-memory dataframe and the query is executed in the datasource so that the data is available in the datasource for further SQL operations. This function is intended only to small amounts of data which is less than 16384 rows.

Example: Select matching cases from events data using in-memory dataframe: 

let model = ModelById(1);
let dfEvents = model.EventsDatatable.SqlDataFrame;
let inMemoryDf = ToDataFrame(
  [["1"], ["2"], ["3"]],
  [#{"Name": "id", "DataType": "String"}]
);
model.ToSqlDataFrame(inMemoryDf)
  .Join(dfEvents, ["id": "CaseId"])
  .SelectDistinct(["CaseId"])
  .Collect();
TriggerNotifications (Boolean) Notification names (String*) Triggers the given notifications for the Model. Notifications are given by their names. Triggering means that the configured rules are run and notification emails are sent as defined by the rules. If the notification names parameter is not provided, all notifications in the Model are triggered.

The function return true if any notification were triggered, otherwise false. 

ModelById(123).TriggerNotifications(["Notification 1", "Notification 2"]);
Triggers notifications Notification 1 and Notification 2 in model id 123.

ModelById(123).TriggerNotifications();
Triggers all notifications in model id 123.

Function to get Model by model id:

Functions Parameters Description
ModelById
  • Model id (Integer)

Returns Model object corresponding to the provided model id.

Object-centric model

Object-centric models additionally have the following properties and functions.

Object-Centric model properties Description
IsOcelModel (boolean) Returns true when the model is an OCEL model.
OcelEvents (Datatable) Datatable containing event data for the OCEL model. Value null is returned if event datatable has not been configured for the model. Throws an unsupported operation exception if the model is not an OCEL model.
OcelEventToObject (Datatable) Datatable containing event-to-object relations data for the OCEL model. Value null is returned if event-to-object relation datatable has not been configured for the model. Throws an unsupported operation exception if the model is not an OCEL model.
OcelEventTypes (Dictionary) Returns a dictionary containing event type names as keys and the datatables holding event data for that event type in this OCEL model as value. An empty array is returned if event types datatable has not been configured for the model. Throws an unsupported operation exception if the model is not an OCEL model.

Example: Get datatable for "Create order" events: 

ModelById(1).OcelEventTypes.Get("Create order")
OcelObjects (Datatable) Datatable containing objects data for the OCEL model. Value null is returned if object datatable has not been configured for the model. Throws an unsupported operation exception if the model is not an OCEL model.
OcelObjectToObject (Datatable) Datatable containing object-to-object relations data for the OCEL model. Value null is returned if object-to-object relation datatable has not been configured for the model. Throws an unsupported operation exception if the model is not an OCEL model.
OcelObjectTypes (Dictionary) Returns a dictionary containing object type names as keys and the datatables holding data for that object type in this OCEL model as value. An empty array is returned if object types have not been configured for this model. Throws an unsupported operation exception if the model is not an OCEL model.
Object-centric model functions Parameters Description
OcelEventType Event type name (String)

Datatable containing event type attributes of given event type in this OCEL model. Value null is returned if a datatable is not configured for this model for given event type. Throws an unsupported operation exception if the model is not an OCEL model.

Example: Get datatable for "Create order" events: 

ModelById(1).OcelEventType("Create order")
OcelObjectType Object type name (String) Datatable containing object type attributes of given object type in this OCEL model. Value null is returned if a datatable is not configured for this model for given object type. Throws an unsupported operation exception if the model is not an OCEL model.

Project

Project properties Description
CreatedBy (User) User who created the Project.
CreatedDate (DateTime) Timestamp when the Project was created.
Configuration (Dictionary) Project settings as Dictionary object.
ConfigurationJson (String) Project settings as json string. 
{
  "ConnectionStringKeys": {
    "Snowflake": "MyKey1",
    "SqlServer": "MyKey2"
  }
}
Dashboards (Dashboard*) Returns all dashboards in the project.
Datatables (Datatable*) Returns all Datatables in the project.
DeletedDate (DateTime) Timestamp when the Project was deleted (moved to the recycle bin).
Description (String) Project description. The project description may contain line breaks.
DeletedBy (User) User who deleted the Project (moved to the recycle bin).
Id (Integer) Id of the Project.
LastModifiedBy (User) User who last modified the Project.
LastModifiedDate (DateTime) Timestamp when the Project was last modified (refers to the project name, description and parent, not the contents of the project).
Name (String) Name of the Project.
Models (Model*) Models that are in the Project.
Parent (Project) Parent project, i.e. a Project where the Project is located in the hierarchy of Projects. Returns null for root level Projects. Throws an error if user doesn't have access to the parent project.
ParentProjectId (Integer) Parent project id. Returns null for root level Projects. The parent project id is returned even if user doesn't have access to the parent project.
Scripts (Script*) Scripts that are in the Project.
Secrets (Dictionary*) Returns array of all secrets in the project as Dictionary with following properties:
  • Name (string): Name of the secret.
  • Type (string): Type of the secret which is one of the following: "odbc", "sap", "salesforce".
Project functions Parameters Description
CreateDashboard (Dashboard) Parameters dictionary Creates a dashboard to the project. EditDashboards permission to the project is required. The parameter is dictionary with following supported dashboard properties:
  • Name (String): Name of the dashboard.
  • Identifier (String): Identifier of the dashboard.
  • Description (String): Description of the dashboard.
  • Content (Dictionary): Content of the dashboard.

Example: Create empty dashboard. 

ProjectById(1)
  .CreateDashboard(#{
    "Name": "My dashboard",
    "Identifier": "MyDashboard"
  });

Example: Create dashboard with a chart. 

ProjectById(1)
  .CreateDashboard(#{
    "Name": "My dashboard",
    "Content": #{
      "version": 4,
      "typeName": "View",
      "name": "My dashboard",
      "subElements": [
        #{
          "position": #{
            "x": 0,
            "y": 0,
            "width": 0.5,
            "height": 0.5,
            "zOrder": 0
          },
          "element": #{
            "typeName": "Chart",
            "configuration": #{
              "root": #{
                "expressionType": "Cases",
                "expressionParameters": #{}
              },
              "measures": [#{
                "expressionType": "Count",
                "expressionParameters": #{}
              }]
            }
          }
        }
      ]
    }
  });
CreateDatatable (Datatable)
  • Parameters dictionary
 Creates datatable to the project. After creation, there are no columns or rows in the datatable. The function returns the created datatable entity. Following properties can be set for the datatable:
  • Name (string): Name of the datatable. This parameter is mandatory.
  • Description (string): Description for the datatable. This parameter is optional.
  • NameInDataSource (string): Table name in the datasource (e.g., in Snowflake) which this datatable is linked to. This parameter is optional.
  • SchemaNameInDataSource (string): Schema name in the datasource (e.g., in Snowflake) which this datatable is linked to. This parameter is optional.
  • DatabaseNameInDataSource (string): Database name in the datasource (e.g., in Snowflake) which this datatable is linked to. This parameter is optional.
  • Type (string): Defines where the data for the datatable is located. Available values are Snowflake, SqlServer, and Local. This parameter is optional and default value is defined by the DefaultDataSource setting.
  • Connection: Connection object for the datatable. This parameter is optional.

Example: Create a new datatable: 

ProjectById(1).CreateDatatable(#{
  "Name": "My datatable"
});

Example: Create Snowflake datatable linked to a custom table: 

ProjectById(1).CreateDatatable(#{
  "Name": "My datatable",
  "Description": "My description",
  "NameInDataSource": "MyTable",
  "SchemaNameInDataSource": "MySchema",
  "DatabaseNameInDataSource": "MyDatabase",
  "Type": "Snowflake"
});

Example: Create Snowflake datatable where connection string is stored as a secret: 

ProjectById(1).CreateDatatable(#{
  "Name": "My datatable",
  "Type": "Snowflake",
  "Connection": ProjectById(1).CreateSnowflakeConnection(#{ "OdbcConnectionStringKey": "MyKey" })
});
CreateModel (Model)
  • Parameters dictionary
 Creates a model to a project. Requires GenericWrite permission for the Project and global CreateModel permission. If a model with that name already exists, an exception is thrown.

Parameters dictionary has the following properties:

  • Name: Name of the model. This property is mandatory.
  • Description: Description of the model. This property is optional.
  • Configuration: Configuration dictionary for the model. This property is technically optional, but a working model requires at least datasource settings to be defined.

Example: 

ProjectById(1).CreateModel(#{    
  "Name": "My model",
  "Description": "My description",
  "Configuration": #{
    "DataSource": #{
      "Cases": #{
        "DataSourceType": "datatable",
        "DataTableName": "My cases datatable",
        "Columns": #{
          "CaseId": "Case Name"
        }
      },
      "Events": #{
        "DataSourceType": "datatable",
        "DataTableName": "My events datatable",
        "Columns": #{
           "CaseId": "Case Name",
           "EventType": "Event Type",
           "Timestamp": "Start Time"
        }
      }
    }
  }
});
DatatableByName (Datatable) Datatable name (String)

Returns Datatable by its name located in the project. Returns null, if Datatable with that name does not exist in the project.

Example: 

ProjectById(123).DatatableByName("MyDatatable1")

Example: Get datatable by name, and create it if it doesn't exist: 

let project = ProjectById(123);
let datatableName = "MyDatatable1";
let datatable = project.DatatableByName(datatableName);
if (datatable == null) {
  datatable = project.CreateDatatable(datatableName);
}
DeletePermanently (none) Deletes the Project permanently. Note that the Project doesn't need to be in the recycle bin to be able to delete it permanently.
Restore (none) Restores the Project from the recycle bin back to the original location.
ModelByName (Model) Model name (String)

Returns Model by its name located in the project. Returns null, if Model with that name does not exist in the project.

Example: 

ProjectById(123).ModelByName("My Model 1")
Modify (Project) Dictionary of settings to change

Change project settings. Following settings are supported:

  • Name (String): Name of the project.
  • Description (String): Description text of the project.
  • ParentProjectId (Integer): Parent project id. Changing this effectively moves the project into different parent project.
  • SnowflakeConnectionStringKey (String): Snowflake connection string key for the project. Snowflake datatables in the project will use connection string behind this key (unless specified by the datatatable).
  • SqlServerConnectionStringKey (String): SQL Server connection string key. SQL Server datatables in the project will use connection string behind this key (unless specified by the datatatable).

ManageProject permission is needed to set SnowflakeConnectionStringKey and SqlServerConnectionStringKey.

Example: Change project name and move project into other parent project: 

ProjectById(1)
  .Modify(#{
    "Name": "Project 1"
    "ParentProjectId": 2
});

Example: Set Snowflake connection string key for the project: 

ProjectById(1)
  .Modify(#{
    "SnowflakeConnectionStringKey": "MyKey1"
});
ScriptByName (Script) Script name (String)

Returns Script by its name located in the project. Returns null, if Script with that name does not exist in the project.

Example: 

ProjectById(123).ScriptByName("MyScript1")
SetSecret
  1. Secret type (string)
  2. Secret name (string)
  3. Secret value (string)
 Sets or adds a secret for the project. Setting the secret value to null removes the secret. There can be several secrets with the same name in the same project if the type of the secret is different. Setting secrets requires the project specific ManageProject permission.

Parameters:

  1. Type (string): Secret type which is one of the following:
    • "externaldatatableconnection": ODBC connection string used by a datatable (e.g., connection string to Snowflake or SQL Server database)
    • "odbc": ODBC connection string (e.g., to extract data, or load a model directly)
    • "sap": SAP password
    • "salesforce": Salesforce password
  2. Name (string): Secret name, used to refer to the secret in the commands.
  3. Value (string): Secret value which contains the confidential information.

Example: Set SAP password: 

ProjectById(1).SetSecret("sap", "MySapPassword", "I l0ve 5AP!");

Example: Remove SAP password: 

ProjectById(1).SetSecret("sap", "MySapPassword", null);

Function to get Project by id:

Functions Parameters Description
ProjectById Project id (Integer)

Returns project object corresponding to the provided project id.

ProjectByName Project name (String)

Returns project object by given project name. If there is no such project or user doesn't have access to it, null value is returned. If there are multiple projects with the same name, one of them is returned.

Example: 

let project = ProjectByName("My Project");

Script

Scripts are entities that contain executable code, that can be run. Usually scripts contains ETL routines but also other kind of tasks are possible.

Script properties Description
Code (String) Script code.
CreatedBy (User) User who created the Script.
CreatedDate (DateTime) Timestamp when the Script was created.
CurrentRunStart (DateTime) Timestamp of the current run start. Null if the script is currently not running.
Description (String) Description of the Script.
Id (Integer) Id of the Script.
Language (String) Either of the following scripting language: Expression or SQL. When language is Expression, the script is run as an expression script, and when language is SQL, the script is run as an SQL script (using the sandbox database).
LastModifiedBy (User) User who last modified the Script.
LastModifiedDate (DateTime) Timestamp when the Script was last modified.
LastRunEnd (DateTime) Timestamp of the last completed script run end (either successful completion or failure). Null if the Script hasn't been run yet.
LastRunResult (String) Result of the last run. Options are:
  • Completed: The last run was completed successfully.
  • Failed: An error occurred during the last run, so likely the script did not complete as intended.
  • Aborted: Script run was manually stopped prematurely by a user, so the script did not proceeded in the end.

Null if the Script hasn't been run yet.

LastRunStart (DateTime) Timestamp of the last completed script run start time. Null if the Script hasn't been run yet.
Name (String) Name of the Script.
OperationId (Integer) Id of the operation which runs the Script. Null if the script is currently not running.
Project (Project) Project where the Script is located. Null if the script is in the global context.
ProjectId (Integer) Id of the project where the Script is located. Null if the script is in the global context.
Status (String) Current status of the script. Options are:
  • Ready: Script is not running. In this status, the script can be started (changing the status to Running).
  • Running: Script is running. In this status, the script can be stopped (changing the status to Stopping). Calling stop just requests a script to stop, and the actual stopping occurs some time later.
  • Stopping: Script has been requested to be stopped, but it's still running. In this status, neither start nor stop can be called for the script. When the script eventually stops, its status changes to Ready.
Script functions Parameters Description
Run (Object) Dictionary of parameters

Runs the script using the provided parameters. The parameters are available in the script as variables (see the example). Any type of variables can be passed to the script. Note that if the script assumes certain variables, but that they are not passed to the script, the script run will throw an error.

For SQL scripts, the passed parameters are available in the script as variables in format @_parameter_<ParameterName> where <ParameterName> is the name of the parameter, e.g. parameter_myParameter1. Only string type of parameters can be used, so any other type of data in parameter values is converted into strings.

The return value of the script is returned by the Run function. Expression scripts return a value with the return statement or alternatively the result of the last line of the script is the return value. If the script does not return any value, the Run function returns _empty. For SQL scripts, the return value is the last dataset produced by the script (returned as a DataFrame) (SQL scripts might create several datasets using the --#ShowReport command or the Show parameter).

When a script is called using the Run function, the called script status does not change, because it's the parent script that is Running. Also the called script log is not filled, but instead the logging goes to the calling script.

It's possible to call a script using the Run function several times simultaneously.

If there is an error when running the called script, the Run function throws the error to the calling script.

Scripts are run in the script entity context, so for example the following properties are available:

  • Id: Script id
  • Name: Script name
  • Project.Id: Project id where the script is located
  • Project.Name: Name of the project where the script is located

Example: Following script (id 123) raises a specified number to a specified power: 

return Pow(numberToRaise, exponent);

The script can be called as follows (returning 16): 

let runResult = ScriptById(123).Run(#{
  "numberToRaise": 4,
  "exponent": 2
})
Start Dictionary of parameters Starts the script. The function call doesn't wait for the script run to complete (i.e., asynchronous behavior) which is sane as starting the script in the Workspace.

Parameters to the script can be provided as a dictionary of name-value pairs (which is not possible when script is started in the Workspace). Return value is the script run id (integer) if the script was started. If the script is already running, return value is null.

Example: Start script (without parameters) and store the script run id: 

let runId = ScriptById(1).Start();

Example: Start script with passing parameters: 

ScriptById(1).Start(#{
  "variable1": "val1",
  "variable2": 5
});
Stop Stops the script. The operation doesn't wait for the stopping to complete (i.e., asynchronous behavior) which is same as stopping the script in the Workspace. Depending on the operation that the script is performing, the stopping might take some time.

Return value is the script run id (integer) if the script was running. If the script isn't running, the return value is null.

Example: 

let runId = ScriptById(1).Stop();

Function to get a script by the script id:

Functions Parameters Description
ScriptById
  • Script id (Integer)

Returns Script object corresponding to the given script id. If script with the given id doesn't exist or user doesn't have permissions to it, an error is given.

User/Group

User objects represents users and user groups. Note that some properties can only be used for users and some for groups.

User/group properties Description
Description (String) Description of the user.
Email (String) Email address of the user.
FullName (String) Full name of the user or group name.
GlobalPermissions (String*) Array of global permissions of the user. Global permissions come from the global roles assigned to the user and groups that the user belongs to. Note that to get the effective permissions for certain objects, also project specific permissions need to be taken into account.
GroupMemberNames (String*) Array of names of members of a user group. This property is available for groups.
GroupMembers (User*) Array of members of a user group. This property is available for groups.
GroupNames (String*) Array of names of user groups the user belongs to. This property is available for users.
Groups (User*) Array of user groups the user belongs to. This property is available for users.
HasPassword (Boolean) Returns true if user has a password defined in QPR ProcessAnalyzer and thus user can authenticate using the password. If user doesn't have a password, the SAML authentication is the only way to log in. ManageUsers permission is needed to access this property for other users.
Id (Integer) Id of the user, which is unique for every user.
IsActive (Boolean) Returns true only if the user is active (not disabled).
IsGroup (Boolean) Returns true if the user is a user group.
IsLocked (Boolean) Returns true if user account is currently locked. ManageUsers permission is needed to access this property.
LastLockedDate (DateTime) Returns date when user account was locked the last time. Returns null if the user account has never been locked. ManageUsers permission is needed to access this property.
LastLoginDate (DateTime) Returns date when the user made last successful login. ManageUsers permission is needed to access this property for other users.
Name (String) Login name of the user or group.
Roles (Object**)

Returns all roles of the user (both global and project roles) as a nested array structure.

Example: 

ToJson(Users.Where(name == "qpr").Roles)
Returns (for example):
[
  [{"calcId": "Project:1"}, "Administrator"],
  [{"calcId": "Project:2"}, "Analyzer"],
  [{"calcId": "Project:3"}, "Viewer"],
  [null, "RunScripts"],
  [null, "Administrator"]
]
User/group functions Parameters Description
EffectivePermissionsFor (String Array)
  • Project to get permissions

Returns effective (actual) permission of the user to the given project. Project is given as a project object (not as a project id). Effective permissions determine the actual permissions that the user has, i.e. a combination of all permissions assigned to the user and groups the user belong to, including both project specific and global roles.

Permissions for the EffectivePermissionsFor function are as follows:

  • All users can query their own permissions
  • To get permissions for any user, the user needs to have ManageUsers permission.

Note that inactive users don't have any effective permissions, so the EffectivePermissionsFor function does not return any permissions for those users.

Examples: 

EffectivePermissionsFor(ModelById(1234).Project)
Returns (for example): ["EditDashboards", "Filtering", "GenericRead"]
GetAttribute

Returns user attribute value by given attribute name and optionally by the model/project/dashboard context. Supported data types are String, Integer, Float, and DateTime. To store more complex data types, data can be converted into json and stored as string. If the attribute doesn't exist, null is returned.

For example, if using dashboard as context, the attributes are effectively bound to each user and each dashboard separately. Thus, there can be several attributes with the same name as long as the dashboard is different.

Parameters:

  1. Attribute name (String): Name of the attribute.
  2. Attribute context (Project/Model/Dashboard): Optional context object the attribute is linked to.

Users have permissions to get attributes for themselves, and also (administrator) users with global ManageUsers permission can get attributes for all users. In addition, if using the context object, the GenericRead permission is required for the context object.

Example: Get user attribute MyDataValue for myself: 

CurrentUser
  .GetAttribute("MyDataValue");

Example: Get user attribute MyDataValue for user John: 

Users
  .Where(Name=="John")
  .GetAttribute("MyDataValue");

Example: Get user attribute MyDataValue for user 1 related to dashboard id 1: 

UserById(1)
  .GetAttribute("MyDataValue", DashboardById(1));
SetAttribute

Sets user attribute value for given attribute name and optionally for the model/project/dashboard context. Supported data types are String, Integer, Float, and DateTime. To store more complex data types, data can be converted into json and stored as string. If setting value null, the user attribute is removed. Required permissions are same as in the GetAttribute function.

Parameters:

  1. Attribute name (String): Name of the attribute.
  2. Attribute value (String/Integer/Float/DateTime): Attribute value to be stored.
  3. Attribute context (Project/Model/Dashboard): Optional context object the attribute value is linked to.

Example: Set user attribute MyDataValue for myself: 

CurrentUser
  .SetAttribute("MyDataValue ", "value");

Example: Set value 123 as user attribute MyDataValue for user John: 

Users
  .Where(Name=="John")
  .SetAttribute("MyDataValue", 123);

Example: Set current time as user attribute MyDataValue for user 1 related to dashboard id 1: 

UserById(1)
  .GetAttribute("MyDataValue", Now, DashboardById(1));

Function to get User by user id:

Functions Parameters Description
UserById
  • User id (Integer)

Returns User object corresponding to the provided user id.

Retrieved from "https://wiki.onqpr.com/pa/index.php?title=QPR_ProcessAnalyzer_Objects_in_Expression_Language&oldid=25318"