Aggregates reference table data with optional entity and date/time grouping, and allows SQL-like WHERE clauses to filter the reference table data before aggregation.
This function aggregates reference table data for use in P# scripting and Power BI dashboards, optionally grouping the data by entity and date/time, and allowing for filtering of the data before aggregation. Reference tables are an alternative data storage concept provided by the PetroVisor platform. For example, reference tables might be used when there is a many-to-one relationship between the data and the entity and/or date associated with the data. An example of data suitable for reference tables is detailed accounting transaction data.
Syntax
RefTableValueAggregated(
ReferenceTableName as string,
Aggregation as aggregationType,
ColumnName as string,
ColumnUnit as string,
EntityName as string,
DateTime as timeStamp or TimeStampOption as string,
optional WhereExpression as string
)
Aggregation offers a variety of descriptive statistics, such as MIN, MAX, SUM, COUNT, and many more.
ColumnName is the name of one of the columns in the reference table.
ColumnUnit must be a valid unit for the type of data stored in column ColumnName. A reference table column is stored in based units that are set at the time the reference table is created. However, the PetroVisor platform is capable of automatically converting the column data to any other compatible unit. For example, if the column Volume is stored in m3, ColumnUnit can be STB, and PetroVisor automatically converts the data for you.
EntityName can be a specific entity name, or the CurrentEntity() function to pass the current entity in the context to the RefTableValueAggregated() function. EntityName acts as a grouping on the reference table during any aggregations. Reference tables can optionally be constructed without entities in the entity column. For this type of reference table, EntityName can be "None" or the NullString() function.
DateTime and TimeStampOption are mutually exclusive. For DateTime, RefTableValueAggregated() expects a date/time value, such as #7/1/2022# or a function that returns a time stamp, such as CurrentStep(). For TimeStampOption, RefTableValueAggregated() expects one of the following values:
- "None" - does not consider the Time column of the reference table in the aggregation.
- "Current" - uses the current step of the context as a grouping during the aggregation. Same as using DateTime = CurrentStep().
- "First" - uses the start of the current scope in the context as a grouping during the aggregation.
- "Last" - uses the end of the current scope in the context as a grouping during aggregation.
The optional WhereExpression supports SQL-like AND, OR, IS NULL, IS NOT NULL, LIKE, and NOT LIKE to create complex filters on reference tables to filter the data before aggregation. LIKE and NOT LIKE use the '%' (any character) and '_' (any single character) wildcard characters; however, the wildcard character can only appear at the beginning or end of the pattern being searched. For example, patterns like 'INCOME%', '%INCOME', and '_NCOME' are supported, while patterns like 'INCOME%AMOUNT' and 'INC_OME' are not supported.
Example
This example sums accounting transactions in a reference table. The reference table itself has data for both gross and net ownership amounts, and for different revenue and expense categories. The WhereExpression filters the data for only GROSS ownership, ACTIVITY date-based transactions, and a specific collection of revenue categories. The RefTableValueAggregated() function then aggregates the filtered, raw accounting transactions, grouped by entity and date.
Column "Gross Income (Activity)" in "CAD"
RefTableValueAggregated(
"Accounting Transactions Data", // this is the reference table name
Aggregations.Sum, // this is the Aggregation type
"Amount", // this is a column name in the reference table
"CAD", // this is the units of the reference table column "Amount"
CurrentEntity(), // filters to the current entity in the context
"Current", // filters data to the current time step in the context
// this is the SQL-like WhereExpression for further filtering of the
// data in the reference table
"[ownership]='G' and [date_type]= 'ACTIVITY' and ([category]
='GAS INCOME' or [category]='COND INCOME' or [category]
='PROPANE INCOME' OR [category]='BUTANE INCOME' or [category]
='PENTANE INCOME')"
)
End Column
The support of LIKE in the WhereExpression can simplify the WhereExpression in the example above.
Column "Gross Income (Activity)" in "CAD"
RefTableValueAggregated(
"Accounting Transactions Data", // this is the reference table name
Aggregations.Sum, // this is the Aggregation type
"Amount", // this is a column name in the reference table
"CAD", // this is the units of the reference table column "Amount"
CurrentEntity(), // filters to the current entity in the context
"Current", // filters data to the current time step in the context
// this is the SQL-like WhereExpression for further filtering of the
// data in the reference table, using LIKE and the '%' wildcard character
"[ownership]='G' and [date_type]= 'ACTIVITY' and [category] LIKE '%INCOME'"
)
End Column
The CONCAT() function can be used to further expand the functionality of the RefTableValueAggregated() function by embedding signal data and P# script column references into the WhereExpression.
Column "CapEx Drilling" in "CAD"
RefTableValueAggregated(
"Accounting Transactions Data",
Aggregations.Sum,
"Amount",
"CAD",
CurrentEntity(),
"Current",
// in this SQL-like WhereExpression, we are using a reference to another
// column in the P# script to be part of the data filtering of the
// reference table. This could also be a signal in the PetroVisor workspace.
ConCat(
"[ownership] = '",
ToString(Column "Ownership" in " "),
"' and [date_type]='ACTIVITY' and [category]='CAPEX DRILLING'"
)
)
End Column