Name

column — Return a value from the data table.

Syntax

column(label)
column(label, relation)
column(label, relation, period, adjust)
column(label, relation, period, adjust, type)
              

The COLUMN function has the following arguments:

label

The column name that is to be referenced.

relation

(Optional) The name of the relation to retrieve the column from, in case there are duplicate column names.

period

(Optional) A number representing the period to retrieve the value from.

relative

(Optional) A boolean value (true/false) indicating how to treat the period calculation.

type

(Optional) A literal value having the data type that this function should return.

Description

This function will return a value from the data table. In it's simplest form it will retrieve a value from the specified column. This may be used when the column name is made up of characters that are not valid in query language variable names. An example is if a column name contains a hyphen character. The query language would confuse this with a minus operator, possibly resulting in a syntax error. In order to access the field use the column function (e.g. column('AU-ID')).

The function is also useful when the column name itself is composed of an expression (e.g. column(FU+'Pct')).

In the second form, this function will get the value from a column in a specific related table. This may be used when the column name is duplicated across several joined tables. An example is if a set of joined tables have multiple instances of a column named 'AREA'. The usual case is that the parser will select the first column that has this name. The joined tables may be assign alias names, and these can be used to disambiguate which similarly named column should be used. For example,

column('AREA', 'Table3')
                  

See the setShortName method in the AttributeStore class for information about setting the table alias.

In the third form, an offset number may be used to specify the period to retrieve the value from, in case that it is different from the current period. The relative argument is a boolean (true/false) value that indicates if the offset should be added to the default period (relative = true) or if the offset number specifies the period of interest (relative = false). For example,

column('feature.Yield.total', 'Blocks', -1, true)
                  

will retrieve the value from the period prior to the current default period.

For the third form of the first two arguments must be literal string values. The third argument may be any numeric expression (including references to database columns), the result of which will be truncated to an integer value. The fourth argument may be any expression that evaluates to a boolean value.

The fourth form is similar to the third. In this case the first two parameters are allowed to be non-literals, in other words the value of the column or joined table may be read from a database column, e.g.

column('RdLdng_'+PREV_FU, '', 0, true, 0.0)
                  

The final parameter shows the data type that you expect to find in whatever column is ultimately chosen. In the code snip the final parameter is 0.0, so this means expecting to find a number. It could have been a number, an integer, a string, or a boolean (true or false). The actual value of the parameter is ignored, just the data type is used to figure out what kind of calculation is going to be made and what data type the result will be.

It is optional to specify this if the column name and the shortname are literals (e.g. a name in a string, like 'RdLdng_BW1'). If the column name is in a variable (like 'RdLdng_'+PREV_FU) then the command language will have no prior knowledge about which column will be picked until PREV_FU is set and therefor no idea of what kind of data type to expect, so it needs a hint.

Examples

Formula

Description

Result

column('AU-ID')Return a value from the AU-ID column. 
column('AREA', 'Table3')Return a value from the AREA column from the relation named Table3. 
column('feature.Yield.total', '', -1, true)Return a value from the feature.Yield.total column from the previous planning period. 
column('RdLdng_'+PREV_FU, '', 0, true, 0.0)Look up the value from the column named PREV_FU, and then lookup the value from the column named 'RdLdng_'+PREV_FU. The expected data type is a number.