LOOKUP (cube, lookUpValue, transactionalDimension, lookupMeasure, returnMeasure, exactMatch)

Description:

This function returns the value from the corresponding cube from the ReturnMeasure member where a corresponding value was found in the LookupMeasure. This function allows you to "Lookup" values in a cube, and based on these values, you can perform any other function or make a decision and thereby generate appropriate fact data depending on pre-defined conditions. The function is intended to mimic the Excel VLOOKUP and HLOOKUP functions. It allows the user to specify a lookup value and the function finds the nearest match to this value. The lookup can be based on either numeric values or string values. The Cube specified cannot have any dimensions that are not in the destination cube with the exception of the "Transactional Dimension" and "Measure" dimension. That is not to say that it can't have fewer dimensions. It is the responsibility of the user to enter the values in the lookup cube in the correct order. This function will not perform any sorting when performing a lookup. The "Exact" parameter specifies that the entire "LookUpValue" must be matched. If this is false then a number or string that falls between two values will return the smaller value. It is important that the user put the items in the correct order especially for the partial matching feature.

Syntax: LOOKUP (Cube, LookUpValue, TransactionalDimension, LookupMeasure, ReturnMeasure, ExactMatch)

• Cube : Specify the name of the cube which contains the "lookup" values that are to be "looked up" for comparison.
• Value : Specify the value or the range that contains the value to be compared to in the lookup list.
• TranDim : Specify the dimension along which to lookup the values. The values along this dimension must be in sequential order.
• LMeasure : Specify the name of the dimension member which is the "Lookup" measure. This dimension member will be searched to find matching values.
• RMeasure : Specify the name of the dimension member which is the "Result" measure. This dimension member is searched to return the matching values corresponding with the "Lookup" measure.
• Exact : Specify the parameter to specify how to match the pointed value to the lookup value, whether it is to be an exact match or not. 0: Not an exact match. 1: Exact match.

Remarks

• The name of the cubes and the dimension names supplied to the function must be a valid, should exist in the database and must be spelled accurately.
• The values along the "Transaction Dimension" are to be sorted in a sequential order so that the correct result is returned by the function.
• All arguments are compulsory and neither argument is optional.

Example

In the Lookup example there are 2 Cubes, the Revenue Cube and the PriceChange Cube. The Revenue cube contains data regarding the number of units sold per month. The PriceChange cube contains information about the unit price. The Lookup function is used in the Revenue Cube to "Lookup" the current price from the PriceChange Cube. Then based on the result of the lookup a second formula in the Revenue Cube calculates the revenue.



In this example the cube containing the value I want to Lookup is "PriceChange" cube. The value I want to lookup is based on the current Month; actually this example uses a property of the current month which returns the Month Number. The number function is then used to convert the result of the Property lookup from text to a Numeric result. The Value, which is the number of the current month, is then compared sequentially along the Transactional Dimension which in this example is called TransactionID. The "Value" is compared to the Lookup Measure which in this example is defined as the member "DateLookup" from the dimension "LookupMeasures". When a match is found by comparing the Value to the Lookup Measure the result is returned from the Return Measure which is specified as the member "PriceChange" from the dimension "LookupMeasures". The final argument in the Lookup function is called Exact; in this example is specified as FALSE (0), which means that if the Lookup Value falls in between two Lookup Measures the smaller value will be returned. For example the first transaction is for the month of January which is represented by the Month Number 1. The second transaction is for the month of May which is represented by the Month Number 5. For the months that fall in between January and May the price for January will be used. The third transaction is for the Month of October which is represented by the Month Number 10. The months that fall between May and October will use the price specified for May.

If the Exact argument is specified TRUE and lookup range does not contain a member which matches the value being looked up then the result will be #VALUE.