RefTableValueAggregatedAsString()

Aggregates reference table data (AVERAGE, COUNT, MIN, MAX, MEDIAN, SUM, etc.), and allows SQL-like WHERE clauses to filter the reference table data before aggregation.

RefTableValueAggregatedAsString() aggregates reference table data for use in P# scripting and Power BI dashboards, returning the results as a string data type. The function can optionally apply filters on any column in the reference table using SQL-like WHERE clauses before aggregation.

If you want to convert the results of RefTableValueAggregatedAsString() to a numeric result, put the RefTableValueAggregatedAsString() function call inside a ToDouble() function call.

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/time associated with the data. An example of data suitable for reference tables is detailed accounting transaction data.

Syntax

RefTableValueAggregatedAsString(
    ReferenceTableName as string,
    Aggregation as aggregationType,
    ColumnName as string,
    ColumnUnit as string,
    optional WhereExpression as string
)

ReferenceTableName is the name of the PetroVisor Reference Table that holds the data being examined.

Aggregation offers a variety of descriptive statistics, such as AVERAGE, COUNT, MIN, MAX, MEDIAN, SUM, and several more.

ColumnName is the name of a data column in the PetroVisor Reference Table that you want to retrieve data from. Data stored in the ColumnName column can be of type text, numeric, date/time or boolean.

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.

The optional WhereExpression can be used on any column in the reference table, including the Entity and TimeStamp columns. So data can be filtered to a specific entity and/or time frame in addition to applying filters to the other data columns in the reference table. Column names must be enclosed in square brackets, like [Entity] or [TimeStamp]. Strings must be enclosed in straight single quotes ('), such as 'ACCT' or 'CAPEX'. Dates can be either in mm/dd/yyyy or yyyy-mm-dd formats, and enclosed in #, such as #1/1/2023# or #2023-01-01#, or straight single quotes ('), such as '1/1/2023' or '2023-01-01'.

There are also special treatments for the Entity and TimeStamp columns. The CurrentEntity() function can be embedded directly in a WhereExpression filter on the [Entity] column. The CurrentStep() function can be embedded directly in the WhereExpression for filtering the [TimeStamp] column. See examples below for illustrations of the correct syntax for the WhereExpression.

In addition, the optional WhereExpression supports a limited set of SQL operators: AND, OR, IS NULL, IS NOT NULL, LIKE, and NOT LIKE to create advanced filters on reference tables to filter the data before aggregation. LIKE and NOT LIKE use the '%' (any character or characters) 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', '_NCOME' and 'INCOM_' are correct applications of the wildcard characters for filtering, while patterns like 'INCOME%AMOUNT' and 'INC_OME' would not represent correct filtering.

Because of the way the WhereExpression parses standard SQL constructs and wildcard characters in the RefTableValueAggregatedAsString() function, it is very important that data in column references to the function follow these two rules:

Rule #1: the data from column references used in your WhereExpression cannot begin or end with either wildcard character ('%' and '_').

Rule #2: the data from column references used in your WhereExpression cannot contain AND, OR, IS NULL, IS NOT NULL, LIKE, and NOT LIKE separated by leading and trailing spaces (for example, " AND " or " OR ").

Violating either of these rules risks returning unreliable results or possibly no results at all.

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 Well A, for dates greater than Jan 1, 2023, only GROSS ownership, ACTIVITY date-based transactions, and a specific collection of revenue categories. The RefTableValueAggregatedAsString() function then aggregates the filtered, raw accounting transactions, grouped by entity and date.

Column "Gross Income" in "CAD"
    RefTableValueAggregatedAsString(
        "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"
        // this is the SQL-like WhereExpression for further filtering of the 
        // data in the reference table
        "[Entity]='WELL A' and [TimeStamp]>=#1/1/2023# and [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" in "CAD"
RefTableValueAggregatedAsString(
    "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"
       // 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, or the shortcut + operator, can be used to further expand the functionality of the RefTableValueAggregatedAsString() function by embedding signal data, P# script column references and P# function calls into the WhereExpression.

Column "CapEx Drilling" in "CAD"
    RefTableValueAggregatedAsString(
        "Accounting Transactions Data",
        Aggregations.Sum,
        "Amount",
        "CAD",
        // 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(
            "[entity] = CurrentEntity() and ",
            "[ownership] = '",
            ToString(Column "Ownership" in " "),
            "' and [date_type]='ACTIVITY' and [category]='CAPEX DRILLING'"
        )
    )
End Column

Column "CapEx Drilling" in "CAD"
RefTableValueAggregatedAsString(
    "Accounting Transactions Data",
       Aggregations.Sum,
       "Amount",
       "CAD",
// 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.
"[entity] = CurrentEntity() and " +
           "[ownership] = '" +
           ToString(Column "Ownership" in " ") +
           "' and [date_type]='ACTIVITY' and [category]='CAPEX DRILLING'"
   )
End Column