Join
Join package creates a target entity where source entities are joined based on one or more pre-existing shared common field(s) or a user-defined join. Joined tables relate through common values, this is the operational control of the Join package. Multiple tables join to create a single output.
Example: Join package
Once source entities are added to the canvas, the Join package is brought to the canvas by double-clicking or dragging the icon. When the square icon becomes a live controller on the canvas, double-click to reveal the criteria and output definition panel.
Double-click on the control to open Join Criteria. Select Add Criteria to define conditions for the join. Use dropdowns to select shared column values on which to join entities.
Qlik Catalog supports the following joins:
|
INNER JOIN |
Returns records from both tables where there is a match on one or more common fields. Records that do not share the common column value(s) are excluded from the target entity. |
|
FULL JOIN |
Returns all records from both tables, merging fields that are user-defined as matching and populating fields with NULL values for either table without corresponding matches. |
|
LEFT JOIN |
Returns records with a match in the left table. |
|
SWAP |
Switches tables from Right to Left and vice versa, essentially creating a Right Join (returning fields from the right table that do not have a match in the left table). |
Join Criteria for an Inner Join
Inner Join returns records for two tables (for the example below, Student_State and Student_Major) only where there is a match on the common field (in this example the records share the common field 'ID').
The following shows sample data for both entities (Student_State, Student_Major) and target entity sample data for Inner Join, Full Join, Left Join, and Swap (right join).
Source and target entities: Join transformations
Note that the left table (Student_State) does not have an entry for ID=12 and the right table (Student_Major) does not have an entry for ID=2
Source: Student_State (Left Table)
Source: Student_Major (Right Table)
Inner Join
Returned an entity with only records (14) where there was a match on the common field (ID) from entities (Student_State and Student_Major). Note the absence of an entry for ID=2 and ID=12 as those records did not share the common field.
Full Join
Returned an entity with all records (16) where fields are populated with NULL values for either table without corresponding matches on the common field (ID).
Left Join
Returned an entity with 15 records where there was a match on the common field (ID) from source entities (Student_State and Student_Major). Note the absence of an entry for ID=12 as the Left Table (Student_State) has join primacy and that data did not include ID=12 therefore that record was excluded.
Swap (right join)
Returned an entity with 15 records where there was a match on the common field (ID) from entities (Student_State and Student_Major). Note the absence of an entry for ID=2 as the right table (Student_Major) has join primacy and did not include ID=2 therefore that record was excluded.
