♦ Applies to: v12+

Apptio Uplifted Content This topic was contributed by Apptio expert and Senior Consultant @Douglas Mahan. Follow Doug's blog for his latest updates.

In this topic:

Overview

This topic will walk you through the basic use of the LookupContains function that is available in Apptio Studio v12.  For a deeper look into the technical details of the formula, please see the LookupContains function description. 

What does LookupContains do?

The LookupContains formula is designed to perform a multi-column partial-match lookup.  Which means the user will not need to have an exact match to return a value while using a lookup.  See the example below.

As you can tell from the above example, the LookupContains function searches through a description field and returns a given value where there is a partial-match.

How do I use the formula?

Below are the steps you can follow to use the LookupContains function.  This high level use case will be taking a list of Fixed Assets and applying an IT Resource Tower to them.

1. Create/Load the Mapping File

You will need to create and load a ‘Fixed Assets to ITRT Mapping’ file.  Here is an example of the kind of file that you would need.

2. Create a new column in the Formula Step

You will need to create a new formula in the Formula Step of the Fixed Assets Data:

3. Start to create the LookupContains formula

 The new column will be called IT Resource Towers and you will start to type out the formula of =LookupContains(. Once you start to write out the formula, Apptio will begin to auto populate the formulas syntax, which will help you populate the formula.

4. Populate the LookupContains formula

 While populating the LookupContains formula, Apptio will provide the available Data Sets and Column Names that can be used to correctly create the formula.  For this example, below is the final formula (=LookupContains(Description,Fixed Assets to ITRT Mapping,Lookup,IT Resource Tower):

As you can tell, the formula is starting with the Description column in the Fixed Assets Data, then looking into the Fixed Assets to ITRT Mapping file. Once looking into the Fixed Assets to ITRT Mapping file, the formula compares the Description column to the Lookup column.  Wherever the function finds a partial-match in the Lookup column, it will return the value in the IT Resource Tower column to the Fixed Assets Data table.  If the function cannot find a match, it will return NULL or BLANK. (Once again, if you would like additional details on the inner workings of this formula, please see the LookupContains function description.

5. Validate the results

Once the formula is completed, you are going to want to validate your results.  To do this, it is recommend that you navigate to the Table Step in the transform pipeline.

With the Table step selected, you will be able to look at the values that have been returned by the formula.

Key Takeaways

• The formula of LookupContains can be used to help categorize a Description column.
• The formula is only able to return values where there is a partial-match between the source file and the lookup file.
• If there are multiple values that can be returned, then Apptio will return either a NULL or {Various}.
• If you would like additional details around the LookupContains formula, or a more technical breakdown of the functionality, please see the LookupContains function description.