Returns one value from a term list. This function is an InterSystems extension to MDX.
is a key value in that term list.
is an optional field (column) name to use to get the value. By default the value
column is returned.
is the value to return if the term list, key, or field cannot be found.
, %LOOKUP returns a single value.
If a cell in a term list defines a valid member reference, such as [Outlet].[h1].[city].[boston]
, then %LOOKUP resolves this reference and does not return the member reference as a literal value. This is mainly to be compatible with term lists created for use with %TERMLIST.
Comparison with Other Term List Functions
The following table compares functions that you can use with term lists:
||Looks up a value, given the key of a term list item. Returns the value of the term list item. You can specify a default value to return.
||A number or a string (which could be the name of a member).
||Returns a field from a term list item. By default, this field is the key field, but you can return another field instead. You can specify a default value to return.
||Returns a set based on the given term list.
||Returns a set.
Consider a term list called VALUES
with one key/value pair:
In this case, you can use %LOOKUP as follows:
SELECT %LOOKUP("Values","CutOff") ON ROWS FROM HOLEFOODS
For another example, the following query returns the list of cities whose population is greater than the cut off value in the term list:
ON ROWS,Outlet.H1.City.CurrentMember.Properties("Population") ON COLUMNS FROM HOLEFOODS