TableMatch function

Sort feature

defaults to ascending if no parameter

sortOrder=desc for descending

Example:

Ascending: Matched Result =TableMatch(Rules_Tablename, Value, sort=ColumnName_to_Sort)

Descending: Matched Result =TableMatch(Rules_Tablename, Value, sort=ColumnName_to_Sort, sortOrder=desc)

• exclude optional param to the tablematch() function.

Example:

=TABLEMATCH(RulesData,Value,exclude=Foo,exclude={Bar},exclude=Baz)

From version 12.5+, the TableMatch() sort column feature also includes an optional "exclude" param to the tablematch() function. See the syntax for example.

Here is an example of a rules table:

The function supports both text and numbers.

Watch this video from Apptio Education Services: The TableMatch Function. Or, browse all Apptio videos.

Rules table values supported

You can preface text with the ! symbol to search for entries that do not contain the text. For numbers, you can use the standard operators: =,<, <=, >, >=,!=. To search for an empty cell, enter the word BLANK in the filter box. To search for cells that are not empty, enter !BLANK in the filter box. The * wild card is supported. The search options are summarized in the table below.

Where to use

This function can be used in Data sets.

Syntax

For versions prior to 12.5:

TableMatch(Rules Table, Column[,
          suppressNullReturnsOnMatch=true])

For version 12.5+:

TableMatch(Rules Table, Column[, specifiedColumn=column_name, useRegex=false,
          suppressNullReturnsOnMatch=true])

Arguments

Rules Table

The name of the table containing the rules.

Column

The column in the rules table that contains the value that will be returned by the function.

Do not use curly braces around the column name.

Example:

Correct: =Tablematch(Rules Table,Server-Unix)

Incorrect: =Tablematch(Rules Table,{Server-Unix})

suppressNullReturnsOnMatch

If this parameter is included, the function will not return null values.

useRegex (Supported in TBM Studio 12.5+; Learn more)

If this parameter is included, he function will use regular expressions when matching. The match table is treated as regular expressions.

For more information on regex syntax or to test your own regex statements, visit http://www.rexegg.com/regex-quickstart.html and regex101.com.

specifiedColumn (Supported in TBM Studio 12.5+; Learn more)

If this parameter is included, the function returns the value from the specified source table column if no rule is matched.

Return type

String

Notes:

This function is not supported in the Ribbon formula bar or in the data preview function on the Data tab.

Examples

=TableMatch(Cost Center Rules,Cost Center, sort=Cost Center, sortOrder=asc, exclude=SomeColumn1, exclude=SomeColumn2)

Assume you have general ledger data such as the following in a table called Cost Center:

To do that, you want to create a new column in the general ledger table shown above called Cost Center. To populate the new column, you create a new data set called Cost Center Rules that will serve as the rules table. The rules table is shown below:

The following TableMatch function is entered as the value for the new column:

=TableMatch(Cost Center Rules,Cost
      Center)

In the function, Cost Center Rules is the name of the rules table and Cost Center is the name of the column containing the results.

After applying the TableMatch function, the general ledger table looks like the following table: