docs.intersystems.com
InterSystems IRIS Data Platform 2019.2  /  InterSystems MDX Reference

InterSystems MDX Reference
%LOOKUP
Previous section           Next section
InterSystems: The power behind what matters   
Search:  


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:
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” in Advanced Modeling for InterSystems Business Intelligence.
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


Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-08-23 05:35:26