Conditional Lookups

[intapiuser]

 Analytical Need 

Using the lookup function to import data from one table to enhance another, based on specific conditions or filters.

This might be required if we wish to import attributes from a table which is not unique. For example, a transposed dimension table.

In the case below, we have an Orders fact table, to which we want to import the customer's shipping country.

The shipping address is located in the address table.

However, the shipping addresses are not the only type of record that exists in the Addresses table. It also contains billing addresses, which are not relevant to our current analysis.

 Modeling Challenge

The lookup function imitates the behavior of a left join + top 1 selection:

It imports a field from one table into another table, by matching two other corresponding fields from both tables.The featured result will be taken from the first record of the unified data source.

This functionality often comes in very handy when importing data between 2 tables that have 1-to-1 or 1-to-many cardinality, since each record in the master table will find only a single matching record and a single respective value. 

However, when working with more delicate relationships, like Many-to-Many (like we can see in our shipping address example) or even Many-to-1, the lookup function might retrieve values sourced from a record that we would not consider most desirable or relevant.

 Solution

Create a designated match column in the remote table, that would contain the matching key only if certain conditions are met.

1. Create a new custom field of the same data type as the required key (If the Customer ID is a string, so should the Billing Customer ID be).
2. In the field's expression, use a simple case statement, to only expose the key if the record meets the required conditions:
3. In the master table (to which you want to import the lookup field), create a new custom field and start phrasing your lookup. The remote match column should refer to the new designated match column that we have created in step 2.

4. Save and build the  Elasticube to submit your changes.