A LOOKUP expression is a simple form of join. It treats the result of a prior statement as a lookup table.
Its syntax is as follows:
<statement>[<expression list>].<attribute>
The expression list corresponds to the grouping attributes of the specified statement. If any of the expressions in the list is NULL, the result is NULL.
Lookup attributes refer to GROUP BYs of the target statement, in order. Computed lookup of indexed values is allowed, which means you can look up related information, such as total sales from the prior year, as shown in the following example:
DEFINE YearTotals AS SELECT SUM(SalesAmount) AS Total GROUP BY Year ; RETURN AnnualCategoryPcts AS SELECT SUM(SalesAmount) AS Total, Total/YearTotals[Year].Total AS Pct GROUP BY Year, Category ; RETURN YoY AS SELECT YearTotals[Year].Total AS Total, YearTotals[Year-1].Total AS Prior, (Total-Prior)/Prior AS PctChange GROUP BY Year