RefTableValueAsString()

Returns data from a PetroVisor Reference Table using properties of the reference table (Entity, Date/Time and Key).

RefTableValueAsString() retrieves information from a PetroVisor Reference Table using properties of the reference table (Entity, Date/Time and a Key), returning the results as a string data type. Using Entity and/or a Date/Time are optional, but a Key column is required for RefTableValueString() to return information. It is important to design a Key column for your reference table that is easy to reproduce in P# if you are going to use RefTableValueAsString() for data retrieval.

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

RefTableValueAsString(
    ReferenceTableName as string,
    Key as string,
    KeyUnit as string,
    ColumnName as string,
    ColumnUnit as string,
    EntityName as string,
    DateTime as timeStamp or TimeStampOption as string
)

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

Key is the name of the Key column in the Reference Table. The Key column is defined when the reference table is first created. Data stored in the Key column can be of type text, numeric, date/time or boolean. For PetroVisor Reference Tables, the combination of the Entity, Date/Time and Key columns must be unique. When Entity and Date/Time are included in the Reference Table dataset, the value of Key can be repeated, since Entity, Date/Time and Key are combined to create a unique key for each row in the Reference Table. If Entity and  Date/Time are not included in the Reference Table dataset, then the value of Key must be unique for each row in the Reference Table.

KeyUnit must be a valid unit for the type of data stored in the Key column. Any reference table column is stored in base 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 Key column Depth is stored in m, KeyUnit can be ft, and PetroVisor automatically converts the data for you.

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 the ColumnName column. Any reference table column is stored in base 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 ColumnName 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 RefTableValueAsString() function. EntityName acts as a filter (or a lookup) on the reference table during data retrieval. 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, RefTableValueAsString() expects a date/time value, such as #7/1/2022# or a function that returns a time stamp, such as CurrentStep() or CurrentScope(). For TimeStampOption, RefTableValueAsString() expects one of the following values:

• "None" - does not consider the Timestamp column of the reference table in the data retrieval.
• "Current" - uses the current step of the context as a grouping during the data retrieval. Same as using DateTime = CurrentStep().

Example

This example retrieves a single accounting transaction from a reference table. The reference table itself has data for both gross and net ownership amounts, and for different revenue and expense categories. The RefTableValueAsString() function returns the transaction Amount for the specified entity and date.

Column "Gross Income" in "USD"
    RefTableValueAsString(
        "Accounting Transactions Data", // this is the reference table name
        "US122345", // this is the Key value for the data being retrieved
        " ", // units of the Key column
        "Amount", // this is a column name in the reference table
        "USD", // 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
    )
End Column

The Key column can be derived from a Column Expression in the P# Script being used, enhancing the flexibility of data retrieval from the PetroVisor Reference Table.

Column "Gross Income" in "USD"
    RefTableValueAsString(
       "Accounting Transactions Data", // this is the reference table name
       Column "My Key" in " ", // this is the Key value for the data being retrieved
       " ", // units of the Key column
       "Amount", // this is a column name in the reference table
       "USD", // 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
   )
End Column

When Entity and/or Date/Time columns are not used in the Reference Table, the RefTableValueAsString() function might look like this.

Column "Gross Income" in "USD"
    RefTableValueAsString(
       "Accounting Transactions Data", // this is the reference table name
       Column "My Key" in " ", // this is the Key value for the data being retrieved
       " ", // units of the Key column
       "Amount", // this is a column name in the reference table
       "USD", // this is the units of the reference table column "Amount"
       "None", // Entity column is not populated with data, so it is ignored
       "None" // Date/Time column is not populated with data, so it is ignored
    )
End Column