Page tree

Release 8.2.1



Contents:

   

Contents:


You can join together data based on the presence of one or more keys in your source dataset and the joined-in dataset or recipe. A join is a data operation in which two or more tables or datasets are merged into one based on the presence of matching values in one or more key columns that you specify. These shared columns are called the join keys of the two sets of rows that you are attempting to join.

Overview

You can join a recipe or dataset to any of the following objects:

  • Another recipe
  • An imported dataset
  • A reference dataset

For more information on the interface for building joins, see Join Window.

Create Join

You can join datasets through the following mechanisms:

  • Flow View: Select a dataset or recipe object. Right-click and select Append Join

    Tip: The join you specify from Flow View is added as the last step to the recipe. If you selected a dataset to which to add the join, a recipe is created from the object, and the join is added as the first step of the new recipe.

    See Flow View Page.

  • Transform Builder: Search for and select join datasets. See Transform Builder.

Joins are created through a workflow process described below. For more information on these steps, see Join Window.

Step - Choose dataset or recipe to join

Steps:

  1. In the Choose dataset or recipe panel:
    1. Search for a dataset or recipe to which you have access. Your search includes objects outside of the current flow.
    2. You can also select from:
      1. Recipes in your current flow
      2. Datasets in your current flow
      3. All datasets to which you have access.
  2. When you have found the dataset to use in your join, click Accept.

Step - Choose join keys and conditions

Steps:

  1. Next, you select the join key columns and other conditions from each dataset.
  2. Join type: Select the type of join to apply. See "Join types" below.
  3. Join keys:The application attempts to find the best columns to match as the join keys. 
    1. Mouse over the percentage match to get more detailed statistics.
    2. To change a join key, mouse over the key name and then click the Pencil icon. Select your new key.
    3. For more information on the options, see "Modify Keys and Conditions" below.
    4. Click Save & Continue.
  4. Click Next.

Example datasets

For discussion purposes, the following datasets are referenced in the sections below. 

  • The CustId column is shared between both datasets. This column is the join key, as there are no matches between the other colums.
  • Some values in CustId in one dataset do not appear in the other. 

Dataset A:

The first dataset to which you are joining in another is typically called the left dataset.

CustIdLastNameFirstName
c001JonesJack
c002KimKen
c003LeeLarry
c004MillerMike
c005

Dataset B:

The second dataset that you are joining in to the first is typically called the right dataset.

CustIdRegionCompanyName
c002EastACME, Inc.
c003WestTrifax, Inc.
c005NorthExample Co.
c006SouthAce Industries

Join types

There are multiple types of joins, which generate very different results. When you perform a join, you specify the type of join that is applied. The joined-together rows that appear in the output dataset are determined by the type of join that you selected and matching of values in the join key columns.

The following are the basic join types. The Example column references Dataset A (left) and Dataset B (right) from above.

Join TypeDescriptionExample
inner joinIf a join key value appears in the left dataset and the right dataset, the joined rows are included in the output dataset.In the above output, rows c002 and c003 are included only.
left joinIn a left join, all of the rows that appear in the left dataset appear in the output, even if there is no matching join key value in the right dataset.

In the above output, rows c001, c002, c003, c004, and c005 are included.

Rows c006 is excluded.

right joinIn a right join, all of the rows that appear in the right dataset appear in the output, even if there is no matching join key value in the right dataset.

In the above output, rows c002, c003, c005, and c006 are included.

Rows c001 and c004 are excluded.

outer joinAn outer join combines the effects of a left and a right join. Each key value from both datasets is included in the output. If the key value is not present in one of the datasets, then null values are written into the columns from that dataset.

In the above output, rows c001, c002, c003, c004, c005, and c006 are included.

Rows c001, c004, c005, and c006 contain some null values.

cross join

A cross join matches every row in the source dataset with a row in the joined-in dataset, regardless of whether the join keys match.

NOTE: A cross join can greatly expand the number of rows in your dataset, which may impact performance.

If Dataset A has 5 rows and Dataset B has 4 rows, the output has 20 rows.
self joinA self join matches the rows in the left dataset with a version of itself (dataset or recipe) on the right side. Some limitations apply.

For more information, see Join Types.

Step - Specify output columns for the join

Steps:

  1. In the Output columns step, you can specify the columns to include in the output dataset.
    1. Include All: To include all columns from the left and right datasets, click the checkbox below All.
    2. Use the Search box to search for specific columns to include or exclude.
  2. Advanced options: See below.
  3. Click Review.

Apply prefix for column names

In the output dataset, the column names are taken directly from the column names in the source dataset. Potential issues:

  • In some cases, source column names may be an exact match between datasets.  
  • For development purposes, you may wish to track the source of a column for a period of time. 

You can apply a prefix to the column names that are sourced from the left dataset, the right dataset, or both.

  • Name prefix for columns in Current data: Enter a text value to include as the prefix to any output columns that are sourced from the current (left) dataset. For example, you could enter left_.
  • Name prefix for columns in Joined-In data: Enter a text value to include as the prefix to any output columns that are sourced from the joined-in (right) dataset. For example, you could enter right_

Apply dynamic updates of selected columns

In the recipe step that produces the join, the columns that you select are mentioned specifically by name. Optionally, you can choose to automatically add in all columns to your output. For example, if your source data for an imported dataset is augmented with 10 new columns, when you re-run your join, those new columns can be automatically added to the output dataset. 

Tip: You should consider using these options if the schema of your data sources is likely to change in the future.

  • Include all columns from Current data: When selected, all columns that are subsequently added to the Current (left) dataset are automatically included as part of the join. 
  • Include all columns from Joined-In data: When selected, all columns that are subsequently added to the Joined-In (right) dataset are automatically included as part of the join. 

Step - Review join

Steps:

  1. In the Review step, you can verify that the specified join is as you expected.
  2. You should review the columns that are previewed as in the data grid.

  3. To add the join as a recipe step, click Add to Recipe.

Modify Keys and Conditions

NOTE: If you modify the selected dataset to join, the joined dataset, the join keys, or the fields to include in the output, subsequent steps in your transform recipe can be broken by the change. After you modify the join, you should select the last step in your recipe to validate all steps in the recipe.

You can apply the following modifications to how keys are matched. To modify a join key and condition, click the Pencil icon in the Join Keys & Conditions panel.

Ignore special characters

Optionally, you can configure the Trifacta application to ignore the following special characters, when matching values in join keys: 

  • Ignore case: Ignore differences in case between values in the join key columns. MyValue matches with MYVALUE
  • Ignore special characters: Ignore special characters that appear in the join key values. 
  • Ignore whitespace: Ignore spaces, tabs, and other whitespace values that may appear in join key values.

Create fuzzy join

fuzzy join applies a fuzzy matching algorithm to String values in the join key column to account for slight differences in how values are written.

NOTE: Fuzzy joins can only be applied to String data types. Other data types cannot be fuzzy-matched using the algorithm.

This algorithm relies on the metaphone function, which attempts to normalize text values based on how the string is spoken by an English speaker. For more information, see https://en.wikipedia.org/wiki/Metaphone.

  • Fuzzy match: Enable fuzzy matching based on English language pronunciation using the metaphone function.

Create range join

NOTE: This feature may need to be enabled in your environment. See Workspace Settings Page.

Values in the join key columns are matched across a range of values, instead of matching single value to single value. When range joins are enabled, you can set the Condition value between the two join key columns when specifying the join keys. For more information, see Configure Range Join.

Add multiple join keys

For more complex join operations, you can add additional join keys to evaluate. Multi-key joins can be helpful for:

  • Providing more finely specified join keys. For example, lastName and firstName.
  • Performance

To add a second join key, click Add when modifying the join keys and conditions. Specify the keys in each dataset as needed.

This page has no comments.