RunQuery Script Examples: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
(Added example for querying all case data columns in a way that supports also calculated case attributes.)
 
(14 intermediate revisions by one other user not shown)
Line 10: Line 10:
</pre>
</pre>


Following command reads filtered rows (Region is Dallas) from a datatable, and stores the result as a temporary table ''#MyTempTable'' in the scripting sandbox. The execution works efficiently as only the filtered rows are moved between the databases.
Following command reads rows from a datatable where ''Region'' is ''Dallas'', and stores the result as a temporary table ''#MyTempTable'' in the scripting sandbox. The execution works efficiently as only the filtered rows are moved between the databases.
<pre>
<pre>
(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '{
(SELECT 'Configuration', '{
"Root": "DatatableById(123).SqlDataFrame.Where(Column(\"Region\") == \"Dallas\").Collect()"
"Root": "DatatableById(123).SqlDataFrame.Where(Column(\"Region\") == \"Dallas\").Collect()"
}')
--#RunQuery
</pre>
Following command reads rows from a datatable where ''Cost'' is less than ''1000'', and stores the result as a temporary table ''#MyTempTable'' in the scripting sandbox. The execution works efficiently as only the filtered rows are moved between the databases.
<pre>
(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '{
"Root": "DatatableById(123).Where(Column(\"Cost\") < 1000).Collect()"
}')
}')
--#RunQuery
--#RunQuery
Line 25: Line 34:
"Dimensions": [
"Dimensions": [
{
{
"Name": "dimension0",
"Name": "Company Code",
"Expression": "Attribute(\"Company Code\")"
"Expression": "Attribute(\"Company Code\")"
}
}
Line 31: Line 40:
"Values": [
"Values": [
{
{
"Name": "measure0",
"Name": "Case count",
"Expression": "Count(_)",
"Expression": "Count(_)",
"AggregationExpression": "Sum(_)"
"AggregationExpression": "Sum(_)"
Line 42: Line 51:
}
}
],
],
"Root": "Root",
"AggregateOthers": true,
"AggregateOthers": true,
"MaximumRowCount": 20,
"MaximumRowCount": 20,
Line 50: Line 60:
</pre>
</pre>


Following command runs an expression query (list cases data for model id 123) and stores results to a datatable ''MyDataTable'' in project id 456:
Following command gets a list of cases and stores results to a datatable ''MyDataTable'':
<pre>
<pre>
(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'ProjectId', '456') UNION ALL
(SELECT 'Configuration', '{
(SELECT 'Configuration', '{
"Dimensions": null,
"Dimensions": null,
"Values": [
"Values": [
{
{
"Name": "dimension0",
"Name": "Case ID",
"Expression": "Name"
"Expression": "Name"
},
},
{
{
"Name": "dimension1",
"Name": "Start time",
"Expression": "StartTime"
"Expression": "StartTime"
},
},
{
{
"Name": "dimension2",
"Name": "End time",
"Expression": "EndTime"
"Expression": "EndTime"
},
},
{
{
"Name": "dimension3",
"Name": "Case duration",
"Expression": "Duration?.TotalDays"
"Expression": "Duration?.TotalDays"
},
},
{
{
"Name": "dimension4",
"Name": "Latest event",
"Expression": "LastEvent.TypeName"
"Expression": "LastEvent.TypeName"
},
},
{
{
"Name": "dimension5",
"Name": "Event count",
"Expression": "Variation.EventTypeCount"
"Expression": "Variation.EventTypeCount"
},
{
"Name": "Event type count",
"Expression": "Variation.UniqueEventTypeCount"
}
}
],
],
Line 89: Line 102:
--#RunQuery
--#RunQuery
</pre>
</pre>
Following command gets a list of events and stores results to a datatable ''MyDataTable'':
<pre>
(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
"Dimensions": null,
"Values": [
{
"Name": "Case ID",
"Expression": "Case.Name"
},
{
"Name": "Event type name",
"Expression": "TypeName"
},
{
"Name": "Event time",
"Expression": "Timestamp"
},
{
"Name": "Difference from previous",
"Expression": "(Timestamp-PreviousInCase.Timestamp)?.TotalDays"
},
{
"Name": "Event index in case",
"Expression": "IndexInCase + 1"
},
{
"Name": "Automation",
"Expression": "Attribute(\"Automation\")"
}
],
"Root": "Events",
"ModelId": 123
}')
--#RunQuery
</pre>
Following command gets a list of event types and stores results to a datatable ''MyDataTable'':
<pre>
(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
"Dimensions": null,
"Values": [
{
"Name": "Event type name",
"Expression": "Name"
},
{
"Name": "Case count",
"Expression": "UniqueCount"
},
{
"Name": "Event count",
"Expression": "Count"
},
{
"Name": "Incoming flows count",
"Expression": "Count(IncomingFlows)"
},
{
"Name": "Outgoing flows count",
"Expression": "Count(OutgoingFlows)"
},
{
"Name": "Looping case count",
"Expression": "let eventtype=_;Count(Cases.Where(EventCountByType(eventtype) > 1))"
},
{
"Name": "Sum of cost",
"Expression": "Sum(Events.Attribute(\"Cost\"))"
}
],
"Root": "EventTypes",
"ModelId": 123,
"ContextType": "eventlog"
}')
--#RunQuery
</pre>
Following command gets a list of datatables and stores results to a datatable ''MyDataTable'':
<pre>
(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
"Dimensions": null,
"Values": [
{
"Name": "Datatable name",
"Expression": "Name"
},
{
"Name": "Project name",
"Expression": "Project.Name"
},
{
"Name": "Datatable id",
"Expression": "ToString(Id)"
},
{
"Name": "Row count",
"Expression": "NRows"
},
{
"Name": "Column count",
"Expression": "NColumns"
},
{
"Name": "Data last imported",
"Expression": "LastImportDate"
}
],
"Root": "Datatables",
"ContextType": "generic"
}')
--#RunQuery
</pre>
Following command gets a list of models and stores results to a datatable ''MyDataTable'':
<pre>
(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
"Dimensions": null,
"Values": [
{
"Name": "Model name",
"Expression": "Name"
},
{
"Name": "Project name",
"Expression": "Project"
},
{
"Name": "Model status",
"Expression": "Status"
},
{
"Name": "Model last modified",
"Expression": "LastModifiedDate"
},
{
"Name": "Memory size",
"Expression": "EstimatedMemory"
},
{
"Name": "Model id",
"Expression": "Id"
}
],
"Ordering": [[
{
"Name": "Model name",
"Direction": "Ascending"
}
]],
"AggregateOthers": false,
"Root": "Models",
"ContextType": "generic"
}')
--#RunQuery
</pre>
Following command gets a list of scripts and stores results to a datatable ''MyDataTable'':
<pre>
(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
"Dimensions": null,
"Values": [
{
"Name": "Name",
"Expression": "Name"
},
{
"Name": "Project",
"Expression": "Project.Name"
},
{
"Name": "Status",
"Expression": "Status"
},
{
"Name": "Language",
"Expression": "Language"
},
{
"Name": "Last modified",
"Expression": "LastModifiedDate"
},
{
"Name": "Last modified by",
"Expression": "LastModifiedBy"
},
{
"Name": "Last run result",
"Expression": "LastRunResult"
},
{
"Name": "Id",
"Expression": "Id"
}
],
"Root": "Flatten(Projects.Scripts)",
"ContextType": "generic"
}')
--#RunQuery
</pre>
Following command reads a datatable and stores its contents to another datatable (id 456):
<pre>
(SELECT 'DataTableId', '456') UNION ALL
(SELECT 'Configuration', '{
"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery
</pre>
Following command reads a datatable and stores its contents to another datatable (name ''MyDatatable'' in project ''MyProject''):
<pre>
(SELECT 'ProjectName', 'MyProject') UNION ALL
(SELECT 'DataTableName', 'MyDatatable') UNION ALL
(SELECT 'Configuration', '{
"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery
</pre>
Following command reads a datatable and stores its contents to another datatable (name ''MyDatatable'' in project id 456):
<pre>
(SELECT 'ProjectId', '456') UNION ALL
(SELECT 'DataTableName', '456') UNION ALL
(SELECT 'Configuration', '{
"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery
</pre>The following command generates a datatable containing all the cases with all their attribute values, including calculated attribute values. (model id: 123, target data table name: "MyDatatable"):<syntaxhighlight lang="sql">
(SELECT 'DataTableName', 'MyDatatable') UNION ALL
(SELECT 'Configuration', '{
  "ModelId": 123,
  "Root": "let _model=_.Model;let _eventLog=_;let dfCases={let attrs = \"\".OrderBy(_model.CaseAttributes, Name);ToDataFrame(_eventLog.Cases.Concat([Name], {let cas = _; attrs.{let att = _; cas.Attribute(att)}}), Concat([\"CaseId\"], attrs.Name))};dfCases"
}')
--#RunQuery
</syntaxhighlight>

Latest revision as of 13:52, 24 January 2023

This page contains script examples for the RunQuery script command. Note that single quotes (') in the Configuration parameter needs to be escaped by using two single quotes (usually expression queries don't contain single quotes).

Following command reads a datatable and stores it as a temporary table #MyTempTable in the scripting sandbox:

(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery

Following command reads rows from a datatable where Region is Dallas, and stores the result as a temporary table #MyTempTable in the scripting sandbox. The execution works efficiently as only the filtered rows are moved between the databases.

(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).SqlDataFrame.Where(Column(\"Region\") == \"Dallas\").Collect()"
}')
--#RunQuery

Following command reads rows from a datatable where Cost is less than 1000, and stores the result as a temporary table #MyTempTable in the scripting sandbox. The execution works efficiently as only the filtered rows are moved between the databases.

(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).Where(Column(\"Cost\") < 1000).Collect()"
}')
--#RunQuery

Following command runs an expression query (dimensioning by Company Code attribute and calculate cases count for each for model id 123) and stores results to a temporary table MyTempTable in the scripting sandbox:

(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": [
		{
			"Name": "Company Code",
			"Expression": "Attribute(\"Company Code\")"
		}
	],
	"Values": [
		{
			"Name": "Case count",
			"Expression": "Count(_)",
			"AggregationExpression": "Sum(_)"
		}
	],
	"Ordering": [
		{
			"Name": "measure0",
			"Direction": "Descending"
		}
	],
	"Root": "Root",
	"AggregateOthers": true,
	"MaximumRowCount": 20,
	"EnableResultCaching": true,
	"ModelId": 123
}')
--#RunQuery

Following command gets a list of cases and stores results to a datatable MyDataTable:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": null,
	"Values": [
		{
			"Name": "Case ID",
			"Expression": "Name"
		},
		{
			"Name": "Start time",
			"Expression": "StartTime"
		},
		{
			"Name": "End time",
			"Expression": "EndTime"
		},
		{
			"Name": "Case duration",
			"Expression": "Duration?.TotalDays"
		},
		{
			"Name": "Latest event",
			"Expression": "LastEvent.TypeName"
		},
		{
			"Name": "Event count",
			"Expression": "Variation.EventTypeCount"
		},
		{
			"Name": "Event type count",
			"Expression": "Variation.UniqueEventTypeCount"
		}
	],
	"AggregateOthers": false,
	"MaximumRowCount": 200,
	"Root": "Cases",
	"ModelId": 123
}')
--#RunQuery

Following command gets a list of events and stores results to a datatable MyDataTable:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": null,
	"Values": [
		{
			"Name": "Case ID",
			"Expression": "Case.Name"
		},
		{
			"Name": "Event type name",
			"Expression": "TypeName"
		},
		{
			"Name": "Event time",
			"Expression": "Timestamp"
		},
		{
			"Name": "Difference from previous",
			"Expression": "(Timestamp-PreviousInCase.Timestamp)?.TotalDays"
		},
		{
			"Name": "Event index in case",
			"Expression": "IndexInCase + 1"
		},
		{
			"Name": "Automation",
			"Expression": "Attribute(\"Automation\")"
		}
	],
	"Root": "Events",
	"ModelId": 123
}')
--#RunQuery

Following command gets a list of event types and stores results to a datatable MyDataTable:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": null,
	"Values": [
		{
			"Name": "Event type name",
			"Expression": "Name"
		},
		{
			"Name": "Case count",
			"Expression": "UniqueCount"
		},
		{
			"Name": "Event count",
			"Expression": "Count"
		},
		{
			"Name": "Incoming flows count",
			"Expression": "Count(IncomingFlows)"
		},
		{
			"Name": "Outgoing flows count",
			"Expression": "Count(OutgoingFlows)"
		},
		{
			"Name": "Looping case count",
			"Expression": "let eventtype=_;Count(Cases.Where(EventCountByType(eventtype) > 1))"
		},
		{
			"Name": "Sum of cost",
			"Expression": "Sum(Events.Attribute(\"Cost\"))"
		}
	],
	"Root": "EventTypes",
	"ModelId": 123,
	"ContextType": "eventlog"
}')
--#RunQuery

Following command gets a list of datatables and stores results to a datatable MyDataTable:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": null,
	"Values": [
		{
			"Name": "Datatable name",
			"Expression": "Name"
		},
		{
			"Name": "Project name",
			"Expression": "Project.Name"
		},
		{
			"Name": "Datatable id",
			"Expression": "ToString(Id)"
		},
		{
			"Name": "Row count",
			"Expression": "NRows"
		},
		{
			"Name": "Column count",
			"Expression": "NColumns"
		},
		{
			"Name": "Data last imported",
			"Expression": "LastImportDate"
		}
	],
	"Root": "Datatables",
	"ContextType": "generic"
}')
--#RunQuery

Following command gets a list of models and stores results to a datatable MyDataTable:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": null,
	"Values": [
		{
			"Name": "Model name",
			"Expression": "Name"
		},
		{
			"Name": "Project name",
			"Expression": "Project"
		},
		{
			"Name": "Model status",
			"Expression": "Status"
		},
		{
			"Name": "Model last modified",
			"Expression": "LastModifiedDate"
		},
		{
			"Name": "Memory size",
			"Expression": "EstimatedMemory"
		},
		{
			"Name": "Model id",
			"Expression": "Id"
		}
	],
	"Ordering": [[
		{
			"Name": "Model name",
			"Direction": "Ascending"
		}
	]],
	"AggregateOthers": false,
	"Root": "Models",
	"ContextType": "generic"
}')
--#RunQuery

Following command gets a list of scripts and stores results to a datatable MyDataTable:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": null,
	"Values": [
		{
			"Name": "Name",
			"Expression": "Name"
		},
		{
			"Name": "Project",
			"Expression": "Project.Name"
		},
		{
			"Name": "Status",
			"Expression": "Status"
		},
		{
			"Name": "Language",
			"Expression": "Language"
		},
		{
			"Name": "Last modified",
			"Expression": "LastModifiedDate"
		},
		{
			"Name": "Last modified by",
			"Expression": "LastModifiedBy"
		},
		{
			"Name": "Last run result",
			"Expression": "LastRunResult"
		},
		{
			"Name": "Id",
			"Expression": "Id"
		}
	],
	"Root": "Flatten(Projects.Scripts)",
	"ContextType": "generic"
}')
--#RunQuery

Following command reads a datatable and stores its contents to another datatable (id 456):

(SELECT 'DataTableId', '456') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery

Following command reads a datatable and stores its contents to another datatable (name MyDatatable in project MyProject):

(SELECT 'ProjectName', 'MyProject') UNION ALL
(SELECT 'DataTableName', 'MyDatatable') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery

Following command reads a datatable and stores its contents to another datatable (name MyDatatable in project id 456):

(SELECT 'ProjectId', '456') UNION ALL
(SELECT 'DataTableName', '456') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery

The following command generates a datatable containing all the cases with all their attribute values, including calculated attribute values. (model id: 123, target data table name: "MyDatatable"):

(SELECT 'DataTableName', 'MyDatatable') UNION ALL
(SELECT 'Configuration', '{
  "ModelId": 123,
  "Root": "let _model=_.Model;let _eventLog=_;let dfCases={let attrs = \"\".OrderBy(_model.CaseAttributes, Name);ToDataFrame(_eventLog.Cases.Concat([Name], {let cas = _; attrs.{let att = _; cas.Attribute(att)}}), Concat([\"CaseId\"], attrs.Name))};dfCases"
}')
--#RunQuery