Joining tables in Data manager

Join is an operation that can be used to manually combine two tables' data, producing varied results depending on the configuration you select.

This allows more granular control over combined tables than with concatenation. See Concatenating tables in Data manager to learn more about concatenation.

The Join operation takes two tables and combines them into one, which will be a combination of the fields in both original tables, based on the overlap of a common value for one or several common fields. There are multiple operators that can be applied to Join operations: Outer, Inner, Left, and Right.

Join operators

There are four join operators: Outer join, Inner join, Left join, and Right join. The selected operator determines which overlapping fields or values are included or excluded.

Outer join

The Outer join operator contains all possible combinations of values from the two tables, if the overlapping field values are represented in either one or both tables. For a more detailed explanation and examples, see the scripting reference entry: Outer.

Example:  

Inner join

The Inner join operator only contains combinations of values from the two tables, if the overlapping field values are represented in both tables. For a more detailed explanation and examples, see the scripting reference entry: Inner.

Example:  

Left join

The Left join operator contains combinations of values from the two tables, if the overlapping field values are represented in the first table. For a more detailed explanation and examples, see the scripting reference entry: Left.

Example:  

Right join

The Right join operator contains combinations of values from the two tables, if the overlapping field values are represented in the second table. For a more detailed explanation and examples, see the scripting reference entry: Right.

Example:  

Joining tables

The Concatenate or join pane is accessed by clicking in Data manager, clicking Concatenate or join, and selecting two tables. When tables are selected in Concatenate or join tables, Qlik Sense analyzes the fields and automatically maps any fields together that match. If there are no clear matches, fields are left unmapped. When the join is applied, mapped fields are combined in the joined table. Unmapped fields are either included as individual fields with null values for the rows where there is no corresponding value, or excluded entirely if there are no overlapping instances of the value.

The first table selected in Concatenate or join tables is set as the primary table, the table to which the other table is joined. The joined table uses the table and field names from the primary table unless these are manually renamed. You can change which table is the primary table with the button. Concatenate or join tables arranges fields in two rows, with the primary table fields in the top row and the secondary table fields on the bottom row. You can swap the primary and secondary tables with the button.

You can use Edit mappings to change the default mapping and select which fields to map, leave unmapped, or to exclude from the joined table. Edit mappings contains a drag and drop interface for editing mappings and the Fields pane, which lists all table fields. Fields can be mapped by dragging them beneath a primary table field. Fields can be added as a new unmapped field by beside the field in the Fields pane or dragging them into the top row of fields. Unmapped fields are marked with in the Fields pane. Fields removed from the joined table are not included in the table and are not available for use in Qlik Sense after joins are applied to the table.

Once mappings are applied and the tables are joined, you cannot edit the mapped fields, but they can be removed from the tables by splitting the joined table, which restores the tables to their original state.

To join tables in Data manager, do the following:

1. Select tables for joining.
2. Optionally, rename the joined table and the field names.
3. Optionally, edit mappings.
4. Select the join operator.
5. Join the tables.

Selecting tables for joining

Do the following:

1. In Data manager, click in the bottom row.

2. Click Concatenate or join.

   The Concatenate or join pane opens.

3. Select two tables.

   The fields of both tables will be mapped or left unmapped in the Concatenate or join tables pane.

4. To preview a sample of unique values in each field, click .
5. To switch the primary and secondary tables, click .

Renaming the table and field names

Do the following:

1. In the Concatenate or join pane, in the table name field, enter a new table name.
2. In a field name field, enter a new field name.

Editing mappings

Do the following:

1. In the Concatenate or join pane, click Edit mappings.
2. To map two fields, click and drag a table field under a primary table field.
3. To add a new unmapped field, click and drag a table field into the upper row of fields.
4. To remove a field from the joined table, in the field click .
5. To return a removed field back to the table, click in the Fields pane, click beside the field.
6. Click Edit mappings to close Edit mappings.

Selecting the join operator

Do the following:

1. In the Concatenate or join pane, click Select action.
2. Select an operator from the list: Outer join, Inner join, Left join, or Right join.

Joining tables

Do the following:

• In the Concatenate or join pane, click Apply.

The tables are now joined.

Splitting joined tables

In cases where joining is no longer needed, you can split the joined tables into their source tables.

Splitting a joined table

Do the following:

1. Select the joined table.

2. Click .

The table is now split into its source tables. All fields in the source tables and their fields have their pre-join names. Splitting a joined table only splits one level of joining, so that any joined tables that were part of the split joined table have their own join preserved.