Expression Script Examples: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
(25 intermediate revisions by 2 users not shown)
Line 1: Line 1:
Expression language can be used to write scripts in QPR ProcessAnalyzer. See how expression scripts can be created in the [[Managing_Scripts#Creating_Script|Workspace]]. For documentation for the syntax, functions and entities can be found from the main page in the [[QPR_ProcessAnalyzer_Wiki#For_Developers|KPI Expression Language]] section.
This page contains script examples written in the QPR ProcessAnalyzer expression language. See how expression scripts can be created in the [[Managing_Scripts#Creating_Script|Workspace]]. For documentation for the syntax, functions and entities can be found from the main page in the [[QPR_ProcessAnalyzer_Wiki#For_Developers|KPI Expression Language]] section.


== Examples ==
=== Call web service===
Contact to a web service, fetch some data, and store it to a datatable.
Contact to a web service, fetch some data, and store it to a datatable.


<pre>
<syntaxhighlight lang="typescript" line>
let datatableName = "Web Service Data";
let datatableName = "Web Service Data";
let webServiceData = ParseJson(ReadWebService(
let webServiceData = CallWebService(
     #{"Address": "https://processanalyzer.onqpr.com/qprpa/api/serverinfo"}
     #{"Address": "https://processanalyzer.onqpr.com/qprpa/api/serverinfo"}
));
);


let targetDatatable = Project.Datatables.Where(name==datatableName);
let targetDatatable = Project.Datatables.Where(name==datatableName);
Line 30: Line 30:
targetDatatable.Import(dataAsDf, #{"Append":true});
targetDatatable.Import(dataAsDf, #{"Append":true});
WriteLog(`${CountTop(dataAsDf.Rows)} rows written to datatable`);
WriteLog(`${CountTop(dataAsDf.Rows)} rows written to datatable`);
</pre>
</syntaxhighlight>


=== Store data to datatable ===
Get all models in the system and store them to a datatable.
Get all models in the system and store them to a datatable.


<pre>
<syntaxhighlight lang="typescript" line>
let newDatatable = Project
let newDatatable = Project
     .CreateDatatable("Models list " + ToString(Now, "dd.MM.yyyy HH:mm:ss"))
     .CreateDatatable("Models list " + ToString(Now, "dd.MM.yyyy HH:mm:ss"))
Line 49: Line 50:
newDatatable.Import(modelsData);
newDatatable.Import(modelsData);
WriteLog(`Datatable ${newDatatable.Id} created.`);
WriteLog(`Datatable ${newDatatable.Id} created.`);
</pre>
</syntaxhighlight>


=== Convert datatable column data ===
This script can be used to convert a single column into numerical data type. To use the script, you need to setup the following in the beginning of the script:
This script can be used to convert a single column into numerical data type. To use the script, you need to setup the following in the beginning of the script:
* Project name where the datatable is located.
* Project name where the datatable is located.
Line 58: Line 60:
Note that the conversion fails, if there is data that cannot be converted into numerical format. The conversion assumes that period (.) is used as the decimal point.  
Note that the conversion fails, if there is data that cannot be converted into numerical format. The conversion assumes that period (.) is used as the decimal point.  


<pre>
<syntaxhighlight lang="typescript" line>
let projectName = "New Project";
let projectName = "New Project";
let datatableName = "qpr processanalyzer events";
let datatableName = "qpr processanalyzer events";
Line 77: Line 79:
])
])
.Persist(datatable.Name, ["ProjectId": project.Id, "Append": false]);
.Persist(datatable.Name, ["ProjectId": project.Id, "Append": false]);
</pre>
</syntaxhighlight>


Instead of converting to numeric (with the ''ToFloat'' function), data can be converted into string using the ''ToString'' function.
Instead of converting to numeric (with the ''ToFloat'' function), data can be converted into string using the ''ToString'' function.
=== Show DataFrame as HTML table ===
This script defines a function to show dataframe as a HTML table, and uses the function for a literal dataframe.
<syntaxhighlight lang="typescript" line>
function dataframeToHtmlTable(df) {
return
`<table>
<tr>
${StringJoin("\r\n\t\t",  + df.columns.`<th>${_}</th>`)}
</tr>
${StringJoin("", df.Rows.(
"\r\n\t<tr>" + StringJoin("", _.`\r\n\t\t<td>${ToString(_)}</td>`) + "\r\n\t</tr>"
))}
</table>`
}
let data = ToDataFrame(
[
["one", "two", "three"],
["four", "five", "six"],
["seven", "eight", "nine"]
],
["Column 1", "Column 2", "Column 3"]
);
return dataframeToHtmlTable(data);
</syntaxhighlight>
=== Copy local datatables to Snowflake ===
<syntaxhighlight lang="typescript" line>
// Copies all datatables in a project to another project including datatable contents.
// Usage instructions:
// 1. Create expression script in the project from where you want to copy the datatables.
// 2. Create a new project named as "<name of the project to be moved> - Snowflake". New datatables will be created here. E.g., when moving project named "SAP_OrderToCash", the target project should be named as "SAP_OrderToCash - Snowflake".
// 3. Run the script.
// NOTE: Columns of type "Any" will be created as "String"-columns in Snowflake, thus it is recommended that actual data types are set for the tables prior to the move.
let sourceProject = Project;
let sourceProjectName = Project.Name;
let targetProjectName = `${sourceProjectName} - Snowflake`;
let targetProject = First(Projects.Where(Name == targetProjectName));
if (IsNull(targetProject)) {
  WriteLog(`Unable to find target project named "${targetProjectName}". Aborting operation.`);
  return;
}
let dts = sourceProject.DataTables;
WriteLog(`Copying all ${CountTop(dts)} data tables found in project "${sourceProject.Name}" (id: ${sourceProject.Id}) to Snowflake in project "${targetProject.Name}" (id: ${targetProject.Id})`);
dts.{
  let sourceDt = _;
  WriteLog(`Starting to copy data table "${Name}" (id: ${Id}) having ${NRows} rows and ${NColumns} columns.`);
  let targetDt;
  targetDt = targetProject.DatatableByName(sourceDt.Name);
  if (targetDt == null) {
    targetDt = targetProject.CreateDataTable(sourceDt.Name, #{"Connection": CreateSnowflakeConnection(#{"ProjectId": targetProject.Id})});
    targetDt.Import(sourceDt.SqlDataFrame);
    WriteLog(`Finished copying data table "${Name}" (id: ${Id}) to table "${targetDt.Name}" (id: ${targetDt.Id})`);
  } else {
    WriteLog(`Datatable already exist "${Name}" (id: ${Id}) to table "${targetDt.Name}" (id: ${targetDt.Id})`);
  }
}
WriteLog(`Finished copying all the data tables found in project "${sourceProject.Name}" (id: ${sourceProject.Id}) to Snowflake in project "${targetProject.Name}" (id: ${targetProject.Id})`);
</syntaxhighlight>
If you don't need to copy the data but only create the Snowflake datatables with columns, you can change the line 22 to
<syntaxhighlight>
targetDt.Import(sourceDt.SqlDataFrame.head(0));
</syntaxhighlight>
=== Copy single datatable to Snowflake ===
This script creates a copy of a single datatable to Snowflake. Replace the ''<tableId1>'' with the id of the source datatable.
<syntaxhighlight lang="typescript" line>
function CopyDataTableToSnowflake(dataTableId)
{
  let sourceDt = DataTableById(dataTableId);
  sourceDt.SqlDataFrame.Persist(`${sourceDt.Name} - Snowflake`, #{"Append": false, "Connection": CreateSnowflakeConnection(#{"ProjectId": sourceDt.Project.Id})});
}
CopyDataTableToSnowflake(<tableId1>);
</syntaxhighlight>
=== Create a copy of a data table that has all Any-type columns changed to String-type columns ===
<syntaxhighlight lang="typescript" line>
function ConvertAnyDataTypesToStringsToNewTable(dataTableId)
{
  let dt = DataTableById(dataTableId);
  let sdf = dt.SqlDataFrame;
  let cts = dt.ColumnTypes;
  cts.{
    let ct = _;
    if (ct.DataType == "Any") {
      let n = ct.Name;
      sdf = sdf.WithColumn(ct.Name, #sql{Cast(Column(Variable("n")), "ShortString")});
    }
  };
  sdf.Persist(`${dt.Name} - Converted`, #{"Append": false, "ProjectId": dt.Project.Id});
}
ConvertAnyDataTypesToStringsToNewTable(<dataTableId>);
</syntaxhighlight>
=== Query number of rows in given data table having a datetime value in given year grouped by month and return resulting table as CSV ===
SqlDataFrame is used in order to prevent loading the whole datatable into memory first. Filtering is performed as first operation in order to minimize the amount of required work for the data source of the data table.<syntaxhighlight lang="typescript" line="1">
DataTableById(<data table id>)
  .SqlDataFrame
  .Where(#sql{2014 == Year(Column("Start Time"))})
  .WithColumn("Month", #sql{Month(Column("Start Time"))})
  .GroupBy(["Month"]).Aggregate(["Count"], ["Count"])
  .OrderByColumns(["Month"], [true])
  .Collect().ToCsv();
</syntaxhighlight>

Revision as of 11:34, 31 May 2023

This page contains script examples written in the QPR ProcessAnalyzer expression language. See how expression scripts can be created in the Workspace. For documentation for the syntax, functions and entities can be found from the main page in the KPI Expression Language section.

Call web service

Contact to a web service, fetch some data, and store it to a datatable.

let datatableName = "Web Service Data";
let webServiceData = CallWebService(
    #{"Address": "https://processanalyzer.onqpr.com/qprpa/api/serverinfo"}
);

let targetDatatable = Project.Datatables.Where(name==datatableName);
if (Count(targetDatatable) == 0) {
	targetDatatable = Project.CreateDatatable(datatableName)
	.AddColumn("Setting name", "String")
	.AddColumn("Setting value", "String")
	.AddColumn("Data read", "DateTime");
} else {
	targetDatatable = targetDatatable[0];
}

let currentTime = Now;
let dataAsDf = ToDataFrame(
	webServiceData.keys.{
        let key = _;
        [key, webServiceData[key], currentTime];
    },
	["Setting name", "Setting value", "Data read"]
);
targetDatatable.Import(dataAsDf, #{"Append":true});
WriteLog(`${CountTop(dataAsDf.Rows)} rows written to datatable`);

Store data to datatable

Get all models in the system and store them to a datatable.

let newDatatable = Project
    .CreateDatatable("Models list " + ToString(Now, "dd.MM.yyyy HH:mm:ss"))
    .AddColumn("Model name", "String")
    .AddColumn("Project name", "String")
    .AddColumn("Created time", "DateTime")
    .AddColumn("Cases", "Integer");
let startTime = Now;
let modelsData = ToDataFrame(
    Models.([Name, Project.Name, CreatedDate, NCases]),
    ["Model name", "Project name", "Created time", "Cases"]
);
WriteLog(`Listing models took ${(Now - startTime).TotalSeconds.Round(2)} seconds.`);
newDatatable.Import(modelsData);
WriteLog(`Datatable ${newDatatable.Id} created.`);

Convert datatable column data

This script can be used to convert a single column into numerical data type. To use the script, you need to setup the following in the beginning of the script:

  • Project name where the datatable is located.
  • Datatable name
  • Name of the column to be converted

Note that the conversion fails, if there is data that cannot be converted into numerical format. The conversion assumes that period (.) is used as the decimal point.

let projectName = "New Project";
let datatableName = "qpr processanalyzer events";
let columnName = "Event order in case";

let project = (Projects.Where(Name==projectName))[0];
let datatable = (project.Datatables.Where(Name==datatableName))[0];
DatatableById(datatable.Id).DataFrame
.SetColumns([
	columnName: () => {
		let data = Column(columnName);
		if (data == null) {
			null;
		 } else {
			ToFloat(data);
		}
	}
])
.Persist(datatable.Name, ["ProjectId": project.Id, "Append": false]);

Instead of converting to numeric (with the ToFloat function), data can be converted into string using the ToString function.

Show DataFrame as HTML table

This script defines a function to show dataframe as a HTML table, and uses the function for a literal dataframe.

function dataframeToHtmlTable(df) {
	return
`<table>
	<tr>
		${StringJoin("\r\n\t\t",  + df.columns.`<th>${_}</th>`)}
	</tr>
	${StringJoin("", df.Rows.(
		"\r\n\t<tr>" + StringJoin("", _.`\r\n\t\t<td>${ToString(_)}</td>`) + "\r\n\t</tr>"
	))}
</table>`
}

let data = ToDataFrame(
	[
		["one", "two", "three"],
		["four", "five", "six"],
		["seven", "eight", "nine"]
	],
	["Column 1", "Column 2", "Column 3"]
);

return dataframeToHtmlTable(data);

Copy local datatables to Snowflake

// Copies all datatables in a project to another project including datatable contents.
// Usage instructions:
// 1. Create expression script in the project from where you want to copy the datatables.
// 2. Create a new project named as "<name of the project to be moved> - Snowflake". New datatables will be created here. E.g., when moving project named "SAP_OrderToCash", the target project should be named as "SAP_OrderToCash - Snowflake".
// 3. Run the script.
// NOTE: Columns of type "Any" will be created as "String"-columns in Snowflake, thus it is recommended that actual data types are set for the tables prior to the move.

let sourceProject = Project;
let sourceProjectName = Project.Name;
let targetProjectName = `${sourceProjectName} - Snowflake`;
let targetProject = First(Projects.Where(Name == targetProjectName));
if (IsNull(targetProject)) {
  WriteLog(`Unable to find target project named "${targetProjectName}". Aborting operation.`);
  return;
}
let dts = sourceProject.DataTables;
WriteLog(`Copying all ${CountTop(dts)} data tables found in project "${sourceProject.Name}" (id: ${sourceProject.Id}) to Snowflake in project "${targetProject.Name}" (id: ${targetProject.Id})`);
dts.{
  let sourceDt = _;
  WriteLog(`Starting to copy data table "${Name}" (id: ${Id}) having ${NRows} rows and ${NColumns} columns.`);
  let targetDt;
  targetDt = targetProject.DatatableByName(sourceDt.Name);
  if (targetDt == null) {
    targetDt = targetProject.CreateDataTable(sourceDt.Name, #{"Connection": CreateSnowflakeConnection(#{"ProjectId": targetProject.Id})});
    targetDt.Import(sourceDt.SqlDataFrame);
    WriteLog(`Finished copying data table "${Name}" (id: ${Id}) to table "${targetDt.Name}" (id: ${targetDt.Id})`);
  } else {
    WriteLog(`Datatable already exist "${Name}" (id: ${Id}) to table "${targetDt.Name}" (id: ${targetDt.Id})`);
  }
}
WriteLog(`Finished copying all the data tables found in project "${sourceProject.Name}" (id: ${sourceProject.Id}) to Snowflake in project "${targetProject.Name}" (id: ${targetProject.Id})`);

If you don't need to copy the data but only create the Snowflake datatables with columns, you can change the line 22 to

targetDt.Import(sourceDt.SqlDataFrame.head(0));

Copy single datatable to Snowflake

This script creates a copy of a single datatable to Snowflake. Replace the <tableId1> with the id of the source datatable.

function CopyDataTableToSnowflake(dataTableId)
{
  let sourceDt = DataTableById(dataTableId);
  sourceDt.SqlDataFrame.Persist(`${sourceDt.Name} - Snowflake`, #{"Append": false, "Connection": CreateSnowflakeConnection(#{"ProjectId": sourceDt.Project.Id})});
}
CopyDataTableToSnowflake(<tableId1>);

Create a copy of a data table that has all Any-type columns changed to String-type columns

function ConvertAnyDataTypesToStringsToNewTable(dataTableId)
{
  let dt = DataTableById(dataTableId);
  let sdf = dt.SqlDataFrame;
  let cts = dt.ColumnTypes;
  cts.{
    let ct = _;
    if (ct.DataType == "Any") {
      let n = ct.Name;
      sdf = sdf.WithColumn(ct.Name, #sql{Cast(Column(Variable("n")), "ShortString")});
    }
  };
  sdf.Persist(`${dt.Name} - Converted`, #{"Append": false, "ProjectId": dt.Project.Id});
}
ConvertAnyDataTypesToStringsToNewTable(<dataTableId>);

Query number of rows in given data table having a datetime value in given year grouped by month and return resulting table as CSV

SqlDataFrame is used in order to prevent loading the whole datatable into memory first. Filtering is performed as first operation in order to minimize the amount of required work for the data source of the data table.

DataTableById(<data table id>)
  .SqlDataFrame
  .Where(#sql{2014 == Year(Column("Start Time"))})
  .WithColumn("Month", #sql{Month(Column("Start Time"))})
  .GroupBy(["Month"]).Aggregate(["Count"], ["Count"])
  .OrderByColumns(["Month"], [true])
  .Collect().ToCsv();