About the LOOKUP Function Syntax

A LOOKUP function is typically used in the logical layer, as an expression in a translated logical table column.

The syntax of the LOOKUP function is as follows: 

Lookup ::= LookUp([DENSE] value_column, expression_list ) | LookUp(SPARSE value_
column, base_column, expression_list )

expression_list ::= expr {, expression_list }

expr ::= logical_column | session_variable | literal

For example:

LOOKUP( SPARSE SnowflakeSales.ProductName_TRANS.ProductName, SnowflakeSales.Product.ProductName, SnowflakeSales.Product.ProductID, VALUEOF(NQ_SESSION."LANGUAGE"))

LOOKUP( DENSE SnowflakeSales.ProductName_TRANS.ProductName, SnowflakeSales.Product.ProductID, VALUEOF(NQ_SESSION."LANGUAGE"))

Note the following:

  • A LOOKUP function is either dense or sparse, and is specified using the keyword DENSE or SPARSE. The default behavior is dense lookup, if neither DENSE or SPARSE is specified. For DENSE lookup, the translation table is joined to the base table through an inner join, while for SPARSE lookup, a left outer join is performed.

  • The first parameter (the parameter after the DENSE or SPARSE keyword) must be a valid value column from a valid lookup table that's defined in the logical layer.

  • If the SPARSE keyword is given, then the second parameter must be a column that provides the base value of the value_column. For DENSE lookup, this base column isn't required.

  • The number of expressions in the expression_list must be equal to the number of the lookup key columns that are defined in the lookup table, which is defined by the value_column. The expression that's specified in the expression list must also match the lookup key columns one by one in order.

    For example:

    • The lookup key for lookup table ProductName_TRANS is both Product_code and Language_Key

    • The expressions in expression_list are SnowflakeSales.Product.ProductID and VALUEOF(NQ_SESSION."LANGUAGE")

    • The meaning of the lookup is:

      return the translated value of ProductName from the translation table with the condition of Product_code = SnowflakeSales.Product.ProductID and Language_Key = VALUEOF(NQ_SESSION."LANGUAGE")