Create Logical Lookup Columns

You use the Expression Builder in the Semantic Modeler to create a logical column that includes the lookup function.

The value of the logical column depends on the language that is associated with the current user.

You create a logical column using a derived column expression in the column properties pane located in the logical table's Columns tab. See Create Derived Columns.

For example to get the translated product name: 

INDEXCOL( VALUEOF(NQ_SESSION."LAN_INT"), "Translated Lookup Tables"."Product". "ProductName", 
LOOKUP( DENSE "Translated Lookup Tables"."Product Translations". "ProductName", "Translated Lookup Tables"."Product"."ProductID", VALUEOF(NQ_SESSION."WEBLANGUAGE")))

LAN_INT is a session variable that's populated by the session initialization block MLS and represents either the base language or other languages:

  • 0 for base language (for example, en - English)

  • 1 for other language codes (for example, fr - French, or cn - Chinese)

WEBLANGUAGE is a session variable that is initialized automatically, based on the language selected when a user logs in.

The INDEXCOL function helps to select the appropriate column. In the preceding example, the expression returns the value of the base column (ProductName) only if the user language is the base language (that is, when the value of session variable LAN_INT is 0). If the user language isn't the base language (when the value of the session variable LAN_INT is 1), then the expression returns the value from the lookup table of the language that's passed in the WEBLANGUAGE session variable.

When you use the DENSE function (shown in the previous example), if there's no value for a column in the translated language, then the lookup function displays a blank entry.

When you use the SPARSE function (shown in the following example), and there is no value for a column in the translated language, then the lookup function displays a corresponding value in the base language. 

INDEXCOL( VALUEOF(NQ_SESSION."LAN_INT"), "Translated Lookup Tables"."Product".
"ProductName", LOOKUP( SPARSE "Translated Lookup Tables"."Product Translations".
"ProductName", "Translated Lookup Tables"."Product"."ProductName", "Translated 
Lookup Tables"."Product"."ProductID", VALUEOF(NQ_SESSION."WEBLANGUAGE")))