Joins
In data sources, joins create associations between tables so that their rows may be presented together in the same report. Multiple joins associate columns across many tables to create data visualizations when used in reports. The number of tables and joins in a dat source will depend on your reporting needs.
When editing a data source, you can use the Joins tab to join different tables. On the Joins tab, the list of selected and derived tables is duplicated in the Left Table and Right Table panels. All joins are in the All Joins | Joins on Selected Table panel.
You can use the All Joins | Joins on Selected Table panel to manage the joins in a particular data source. You can select between All Joins and Joins on Selected Table sections:
All Joins: This section lists all joins defined for the data source.
Joins on Selected Table: This section lists joins defined on a selected table, simplifying the view that you have of many joins.
Creating a Join
To create a join using the Joins tab:
1. From the Lambda Analytics homepage, click View Existing under the Data Sources link on the top navigation bar.
2. Locate your desired data source within the Repository, right-click on its corresponding title, and click Edit from the context menu.
3. In the Data Source Designer, navigate to the Joins tab.
4. Expand your desired tables in both the Left Table and Right Table panels.
5. Select a column in each table has compatible formats and the same logical meaning, and click an appropriate join type. Your new join will then appear in the All Joins | Joins on Selected Table panel.
6. Click Copy if you would like to copy the selected table, which will then appear in both the Left Table and Right Table.
In some cases, you may need to duplicate a table to join it several times without creating a circular join, or to join it to itself. You can also duplicate a table so that it may be joined with different tables for different uses.
7. Click Change ID if you would like to change the name of the selected table. This new name will become the ID of the table throughout the data source, and will be updated whenever it appears in the Data Source Designer.
8. Click Delete to remove the table from both lists. If the deleted table was the only instance of the table, removing it from the Joins tab will also remove it from the list of selected tables on the Tables tab.
After creating a join, one or more join trees will appear on the Calculated, Pre-Filters, and Display tabs. For example, if you join tables "A" and "B," "B" and "C," and then join tables "D" and "E," the result is two join trees. Columns from table "A" and table "C" may appear in the same report because their tables belong to the same join tree. Tables "A" and "D" are thus said to be unjoined, and their columns might not be compared or appear in the same report. Unjoined tables will then appear individually along with join trees.
Join Types
|
Name |
Description |
|
Inner Join |
The result contains only rows where the values in the chosen columns are equal. |
|
Left Outer Join |
The result contains all the rows of the Left Table, paired with a row of the Right Table where the values in the chosen columns are equal or contain blanks. For example, if courses are in the Left Table, the result of a left outer join contains all courses, even if they do not all have any students enrolled. |
|
Right Outer Join |
The result contains all the rows of the Right Table, paired with a row of the Left Table where the values in the chosen columns are equal or contain blanks. For example, if users are in the Right Table, the results of a right outer join contains all users, even if they are not enrolled in any courses. |
|
Full Outer Join |
The result contains all rows from both tables, paired when the joined columns are equal, and filled with blanks otherwise. |
Case Studies
Listed here is an ever-growing collection of case studies focused on making use of the many features available while creating joins. Each case study has a specified outcome, a set of steps to follow, and an example created in full detail from start to finish.
Comments
0 comments
Article is closed for comments.