Skip to main content

Varicent ELT Help Center

Join

Abstract

Add another data set to an existing one by appending columns.

Combine two data sets by appending columns. Match columns based on selected key columns to lookup and pull in the corresponding data. When joining data, ensure that there is at least one common column in both data sets. The common column is known as a key column.

When to use this tool

Use when you need to combine two data sets.

If you want to combine data sets but restrict it to only matching columns, use the Union tool instead.

Select the join type to combine columns:

  • Left: Values appear in the left data set output when joined, regardless of a match.

  • Right: Values appear in the right data set output when joined, regardless of a match.

  • Inner: Values must exist and match in both data sets for the results to appear in the output.

  • Left anti: Values from the left table that don’t have any matching rows from the right table appear in the output.

  • Full outer: Returns all records in both the top (Left) and bottom (Right) data sets, regardless of a match.

Learn more about the join types...

In the advanced settings, you can select different comparison operators. Equal is the default operator. You can choose from the following operators to filter:

Operator

Text

Numeric

Boolean

Dates

Equal

Supported

Supported

Supported

Supported

Less than

Not supported

Supported

Not supported

Supported

Less than or equal

Not supported

Supported

Not supported

Supported

Greater than

Not supported

Supported

Not supported

Supported

Greater than or equal

Not supported

Supported

Not supported

Supported

Like

Supported

Not supported

Not supported

Not supported

Note: If you select the Like operator, the pattern type appears.

Input and output

To use this tool, you need two data sets.

When you run this tool, it appends columns to the primary data set based on the secondary data set. The data type must be the same in both key columns.

Configuration

Use the following configuration options to configure the Join tool.

Configuring the Join tool

  1. In , add your data sources.

  2. Click symon_add_icon.png +Add Tool.

  3. Click See all tools.

  4. In the search bar, search for the Join tool. Click Add tool.

    Tip

    You can also find the Join tool in the Combine section.

  5. Connect the tools to your data sets.

  6. In the configuration pane, enter the following information:

    Table 54. Join tool configuration

    Field

    Description

    Join conditions

    Select the Join type:

    • Left join

    • Right join

    • Inner join

    • Left anti

    • Full outer

    Join column top

    Select the operator to join the top column. For more information, see: Operators.

    Join column bottom

    Select the operator to join the bottom column. For more information, see: Operators.

    Advanced

    Click to access the Advanced section of the configuration.

    Operator

    Select the operator that you want to use. For more information, see: Operators.



Usage example

You have two data sets. The Universe column from the top and bottom data sets are the related columns that will allow us to merge the top and bottom nodes together.

Join2023.png

Suggestions

The Join tool gives you a breakdown of your data sources and offers suggestions within the Row viewer to help you merge your data and get the results that you want:

  • Choose alternative or unique columns: This suggestion shows an overlay over the configuration, showing you what you could change to the selected join columns.

  • Duplicate rows: This suggestion adds the Unique tool between one or both of the incoming data sources if they have duplicate rows. One more both incoming data sources must have duplicate rows.

  • Clean tools: This suggestion shows you tools to clean the columns in the join conditions if the join results are blank.

  • Learn more: This suggestion takes you to the Join tool documentation.