Using LOOKUP expressions for inter-statement references

In EQL, you can define statements and then refer to these statements from other statements via a LOOKUP expression.

Multiple EQL sub-queries can be specified within the context of a single navigation query, each corresponding to a different analytical view, or to a subtotal at a different granularity level. Expressions also can use values from other computed statements. This is often useful when coarser subtotals are required for computing analytics within a finer-grained bucket.

For example, when computing the percent contribution for each sales representative in a given year, you must also calculate the overall total for the year. You can use a lookup table to create these types of queries.

Syntax for LOOKUP expressions

A LOOKUP expression is a simple form of join. It treats the result of a prior statement as a lookup table.

The syntax for a LOOKUP expression is: 
<LookupExpr> ::= <statement-name>[<LookupList>].<attribute-name>
The square bracket operators are literal and are used to identify the record set and grouping attribute, while the dot operator is also literal and is used to identify the field.
The BNF for LookupList is 
<LookupList> ::= <empty>
             ::= <SimpleExpr> [,<LookupList>]
In this BNF syntax, the square brackets indicate the optional use of a second LookupList.

The lookup list corresponds to the grouping attributes of the specified statement. The result is NULL if the lookup list does not match target group key values, or the target column is NULL for a matching target group key values.

Lookup attributes refer to GROUP BY clauses 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
FROM SaleState
GROUP BY Year;

RETURN AnnualCategoryPcts AS SELECT
  SUM(SalesAmount) AS Total,
  Total/YearTotals[Year].Total AS Pct
FROM SaleState
GROUP BY Year, Category;

RETURN YoY AS SELECT
  YearTotals[Year].Total AS Total,
  YearTotals[Year-1].Total AS Prior,
  (Total-Prior)/Prior AS PctChange
FROM SaleState
GROUP BY Year

Using LOOKUP against states

LOOKUP expressions are supported where the target statement is referring to a named state, with the rule that there must be exactly one expression inside the square brackets, which is matched against the target state's primary key.

If you use multiple lookup keys against a state, EQL will return an error message similar to this example that uses two lookup keys: 
In the definition of attribute "x": The LOOKUP expression has 2 lookup value(s); a LOOKUP expression that refers 
to state "Sales" must have exactly one lookup value, corresponding to the state's primary key "SalesID"

Referencing a value from another statement

For example, suppose we want to compute the percentage of sales per ProductType per Region. One aggregation computes totals grouped by Region, and a subsequent aggregation computes totals grouped by Region and ProductType.

This second aggregation would use expressions that referred to the results from the Region aggregation. That is, it would allow each Region and ProductType pair to compute the percentage of the full Region subtotal represented by the ProductType in this Region:

DEFINE RegionTotals AS
SELECT SUM(Amount) AS Total
FROM SaleState
GROUP BY Region;

RETURN ProductPcts AS
SELECT 
  100 * SUM(Amount) / RegionTotals[Region].Total AS PctTotal
FROM RegionTotals
GROUP BY Region, ProductType
The first statement computes the total product sales for each region. The next statement then uses the RegionTotals results to determine the percentage for each region, making use of the inter-statement reference syntax.
  • The bracket operator indicates to reference the RegionTotals result that has a group-by value equal to the ProductPcts value for the Region attribute.
  • The dot operator indicates to reference the Total field in the specified RegionTotals record.

Computing percentage of sales

This example computes for each quarter the percentage of sales for each product type.

This query requires calculating information in one statement in order to use it in another statement.

To compute the sales of a given product as a percentage of total sales for a given quarter, the quarterly totals must be computed and stored. The calculations for quarter/product pairs can then retrieve the corresponding quarterly total. 
DEFINE QuarterTotals AS
SELECT SUM(Amount) AS Total
FROM SaleState
GROUP BY Quarter;

RETURN ProductPcts AS
SELECT 
  100 * SUM(Amount) / QuarterTotals[Quarter].Total AS PctTotal
FROM QuarterTotals
GROUP BY Quarter, ProductType