Name

tablerange — Extract a value from a table using a range lookup.

Syntax

tablerange(tablename, rangecolumn, lookupValue, resultexpression)
              

The TABLERANGE function has the following argument:

tablename

The name of the table that will be used for this lookup operation.

rangecolumn

The name of the column in the lookup table that will be used for the range lookup.

lookupValue

The value to use to for the range lookup.

resultExpression

An expression that will be evaluated to generate the result for the selected row.

Description

This function extracts a value from a lookup table by performing a range lookup on a column, and then returning the evaluation of an expression on the matching row.

The tablename argument is a string literal the specifies the name of a previously defined table.

The rangecolumn argument specify the name of a numeric column in the lookup table that will be used for range matching. The table must be in sorted order based on the range column values.

The lookupValue argument specifies a value that should be used for the range test.

The match proceeds by searching for the last row in the table where the lookupValue is less than or equal to the table value. If the lookupValue is greater than the last entry in the table, the last row will be used. When a match is found the result expression is evaluated and returned.

The result expression is compiled in the context of the lookup table, and so result expression argument must have a string data type. Typically this will be a column name from the lookup table enclosed in single quotes, with the quotes protecting the name from evaluation in the current context where the column is unknown. It is also possible to use variables from the data table in the current context to construct the result expression string. Expressions formed in this way must be compiled for each row at runtime, and you will need to enclose the result expression in a type hint conversion function so that the compiler will know the expected semantic value of the expression. The following examples will describe this in more detail.

Examples

The examples in this section will make use of the following two data tables. This example assume the use in the Patchworks application, but this applies equally to other applications (e.g. MatrixBuilder, MapViewer).

Figure 190. A portion of the block attribute table


Figure 191. The harvest cost lookup table


The objective is to determine a harvesting cost based on the steepness of the site, the average volume harvested per unit area, and the total volume harvested. The block attribute table contains the area (MANAGEDAREA), the total amount of volume harvested (product.total.Yield), and the steepness classification. The harvest cost lookup table has the per unit area harvest volumes (the Vol column), and per unit volume costs by steepness category in the 'low', 'mod' and 'high' columns. The rows of the harvest cost table are sorted in ascending order by the average volume harvested per unit area (in this example the area units are acres and the volume units are MBF).

The block attribute table is available in Patchworks, but we will need to define the harvest cost lookup table using a scripted command such as:

Expression.setTableData("obtCost", new CVSStore("obtCost.csv"));

If using the MatrixBuilder, lookup tables are defined using the table element.

Example 1. Table range lookup with a literal result expression

For this first example we will use a simple set of constant valued inputs that we will compute from the first row of the block attributes table.

The managed area is 3.006 acres and the total harvested volume is 64.023 MBF. We can computed that the average harvested volume is 21.3 MBF/acre. The slope category is 'low', and we can lookup the cost factor and multiply it by the total volume harvested using the following expression:

tableList('obtCost', 'Vol', 21.3, 'low') * 64.023

The lookup in the 'obtCost' table will find the last row where the lookupValue of 21.3 is less than or equal to the value in the 'Vol' column. The value being returned is from the 'low' column of the matching row:

Because the result expression was the literal string 'low', at compile time the expression parser was able to determine the column to retrieve and that the data type of the tablelist function will return is a number data type. The lookup returns a cost value of $105/MBF, and this is multiplied by the total harvest volume amount of 64.023 MBF for a total harvest cost of $6,722.41.

Example 2. Table range lookup with a non-literal value

Let's generalize the previous example so that it will work for all rows in our table. We can replace the lookup value and volume multiplier with the column names from the block attribute table, and we can replace the result column value with slope classification name:

asNumber(tableList('obtCost', 'Vol', product.total.Yield/MANAGEDAREA, SLOPECLS)) * product.total.Yield

After substituting column names for the previous literal values we have a generalized expression that will work for all rows. The result expression of SLOPECLS refers to a column in the block attribute table that contains string values of 'low', 'mod' or 'high', each corresponding to a column in the cost table. We cannot know at compile time which column will be selected, so the compilation of this sub-expression is deferred and will be calculated for each row at runtime.

We use the asnumber function to provide a type hint to the expression parser that we expect a number data type to be returned, and this data type is used to complete the semantic analysis of the expression. Without providing this type hint the expression would have failed to compile. The asnumber function will also perform a runtime check of the data type being returned. If the value is not a number (for example if the column contains a string data type) an error condition will occur. To prevent the potential error a type conversion function (such as number) could be used instead to provide both a type hint and a data conversion if required.

One further caution about this example is that the average volume per unit area is being calculated with a division. Additional care should be taken to make sure that the expression is not attempted on rows where the MANAGEDAREA value is zero, possibly by ensuring that cost calculations are only performed on rows where a harvest has taken place.

Example 3. Table range lookup in the Matrix Builder

The above expression is useful for reports and other queries, but if we want to produce an attribute that can be used in an account we will have to use the Matrix Builder. The syntax is essentially the same although we will refer to curves and attributes rather than to column values.

asNumber(tableList('obtCost', 'Vol', attribute('product.total.Yield'), SLOPECLS)) 
    * attribute('product.total.Yield')

The Matrix Builder deals with per area attribute values, so there is no need to divide by the area of the block. This example assumes that an attribute with a label of 'product.total.Yield' had been defined previously in the ForestModel. The resulting XML fragment would look similar to:

  <products>
    <attribute label="product.cost.obt" output="nonzero">
        <expression statement="asNumber(tableList('obtCost', 'Vol', \
                               attribute('product.total.Yield'), SLOPECLS)) \
                               * attribute('product.total.Yield')" 
                     by="1" ignoreMissingAttributes="true"/>
    </attribute>
  </products>

(Note that the continuation lines used above are for clarity only and are not legal in XML documents.)