Expression Script Examples: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
Line 87: Line 87:
=== Present DataFrame as HTML Table ===
=== Present DataFrame as HTML Table ===
<pre>
<pre>
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(
let data = ToDataFrame(
[
[
Line 95: Line 106:
["Column 1", "Column 2", "Column 3"]
["Column 1", "Column 2", "Column 3"]
);
);
`<table>
 
<tr>
return dataframeToHtmlTable(data);
${StringJoin("\r\n\t\t",  + data.columns.`<th>${_}</th>`)}
</tr>
${StringJoin("", "\r\n\t<tr>" + data.Rows.StringJoin("", "\r\n\t\t<td>" + _.ToString(_) + "</td>") + "\r\n\t</tr>")}
</table>`
</pre>
</pre>

Revision as of 14:07, 28 June 2021

Expression language can be used to write scripts in QPR ProcessAnalyzer. 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.

Examples

Call Web Service

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

let datatableName = "Web Service Data";
let webServiceData = ParseJson(ReadWebService(
    #{"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`);

Read Models Data

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.

Present DataFrame as HTML Table

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);