Skip to main content

%LOOKUP (MDX)

Returns one value from a term list. This function is an InterSystems extension to MDX.

Returned Type

This function returns a number or a string.

Syntax and Details

%LOOKUP(termlist,key,field,default)

Where:

  • termlist is the name of a term list.

  • key is a key value in that term list.

  • field is an optional field (column) name to use to get the value. By default the value column is returned.

  • default is the value to return if the term list, key, or field cannot be found.

Unlike %TERMLIST, %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.

For information on defining term lists, see Defining Term Lists.

Comparison with Other Term List Functions

The following table compares functions that you can use with term lists:

Function Purpose Return Value
%LOOKUP 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).
LOOKUP 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.
%TERMLIST Returns a set based on the given term list. Returns a set.

Example

Consider a term list called VALUES with one key-value pair:

key      value 
CutOff   10000000

In this case, you can use %LOOKUP as follows:

SELECT %LOOKUP("Values","CutOff") ON ROWS FROM HOLEFOODS

==> 1000000

For another example, the following query returns the list of cities whose population is greater than the cut off value in the term list:

SELECT FILTER(Outlet.City.Members,Outlet.H1.City.CurrentMember.Properties("Population")>%LOOKUP("Values","CutOff")) 
ON ROWS,Outlet.H1.City.CurrentMember.Properties("Population") ON COLUMNS  FROM HOLEFOODS

See Also

FeedbackOpens in a new tab