%LOOKUP (MDX)
Returned Type
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
