Key function

Edit online

Applies to: TBM Studio 12.0 and later

The purpose of the Key function is to create a column with unique values for each row in a table. The function defines a new column in a data set as the combination of two or more columns and/or a text string or a formula. Typically, the column would be used as a unit identifier for an object in a model or to establish a relationship in the inference map.

The Key function is the preferred method of creating a key column for a table when you have a data set with more than 1 million rows. It addresses the situation where concatenated columns in a table will not produce a unique value for each row. For example, assume you have the following table:

If you use concatenation to create a third column (={Value 1}&{Value2}), you get the result shown below. Note that the Key Column values are not unique.

If you use the Key function to create a third column (=Key(Value 1,Value2), you get the result shown below. The Key Column values now are unique.

If you used the && concatenation, it would produce unique values in the example given above, but it would not give unique values if you had the following table where a cell in each column is null. When columns Value 1 and Value 2 are combined, they result in the Concat column.

Also, the Key function results in faster system performance than concatenation.

Where to use

This function can be used in:
  • Data sets

Syntax

Key(value1,value2,...)

Arguments

value

A value can be the name of a column, a text string in quotes, or a formula.

Return type

|value1,value2,...|

Note that the bars at the beginning and end of the return value indicate that the value is a combination of the elements. It is not a new text string as you would get if you concatenated two or more columns.

Notes:
  • A value made with the Key() function is only equivalent to other values made with the Key() function. The result from a key function will only be inference-linked to values generated by other Key() functions. Also the trace function in models will now work with Key() function values.
  • If you are working with a data set that has less than 1 million rows, you should use concatenation to create the Key Column. For more information on concatenation, see String concatenation.

Examples

Assume you have the following table:

To add the Key Column shown below, you would enter the following in the Value field for the column:

=key(Application,Service
      Level,Type)

To add the Key Column shown below, you would enter the following in the Value field for the column:

=key("Key:",Application,Service
      Level)