Lookup functions were implemented in order to give to the user the ability of getting values from other datasets.
Unlike Excel Lookup functions, the reference dataset is not open automatically to update lookup values, they are considered more static values then dynamic values. However, if the lookup dataset is open into another frame on any recalculation the new values are updated.
Pros: Values are kept independent of the existence of the original lookup dataset, in more then 90% of the situations are not necessary reread of lookup values.
Cons: To simulate the dynamic behaviour the lookup datset has to be opened in another frame of the running session.
Lookup functions are implemented for columns calculations (LookupText, LookupInteger, LookupDate...) and for parameters (LookupParamText, LookupParamInteger, LookupParamDate...). They are not available in OLAP Calculations.
Syntax is : Lookup...(LookupListID, LookupValueAsText, DefaultValueIfDatasetIsMissing, DefaultValueIfNotFound)
LookupListID is a result of LookupList(DatasetName, DotCommaSeparatedColumnList, ReturningColumn, OptionalStaticFilterAsText), a special function which returns a indicative of an ad-hoc in memory created dictionary. Once dictionary is created, the structure is used to lookup values very fast.
LookupValueAsText is a result of one of the the TextLK, DateLK, ... functiones which builds a friendly lookup value.for the dictionary.
DefaultValueIfDatasetIsMissing is a parameter which is used in case lookup dataset is not found. Usually this is the field itself, which means the function will return existing value if the lookup dataset is not found.
DefaultValueIfNotFound is the returning value for not found values, typicaly values are the empty values.