Join or Merge¶
These are used to link data with another View based on a shared Column.
![]()
Fig. 108 Example of a Join¶
Mammoth provides another function, Lookup to achieve similar results. This task is an advanced version of the Lookup.
This is similar to a JOIN operation in an SQL database or a VLOOKUP in Microsoft Excel.
Quick Start¶
Let us start with the following two views.
Customers → View 1:
ID |
Name |
---|---|
1 |
John |
2 |
Katie |
3 |
Priya |
4 |
Raj |
Orders - View 1
ID |
Customer ID |
Subtotal |
Tax |
Total |
---|---|---|---|---|
1 |
2 |
151 |
15 |
167 |
2 |
1 |
89 |
9 |
98 |
3 |
4 |
89 |
9 |
98 |
The customer names are associated with the orders. Note that the customer ID column has corresponding values in Customer → view 1.
Orders - View 1 do the following:
Open Data Preparation menu and click on Merge & Branch Out.
Select Join;
Select Customers → View 1;
In select common keys section map Customer ID column to ID column;
Select the type of Join;
Select coulmns in the prefix selection box to add a prefix to the column names of Customers View;
Click APPLY.
This is the final result:
ID |
Customer ID |
Subtotal |
Tax |
Total |
Customer.Name |
---|---|---|---|---|---|
1 |
2 |
151 |
15 |
167 |
Katie |
2 |
1 |
89 |
9 |
98 |
John |
3 |
4 |
89 |
9 |
98 |
Raj |
Supported Options¶
The following options are supported in this task:
View to Join with: Allows selecting a view with which to join;
Common keys: Allows mapping between pairs of columns with common values;
Join Type: Allows one of four join types: Left, Right, Inner or Outer;
Mammoth does not allow certain type of joins. For more information, see Restrictions .
Note
You can also join text columns to numeric columns and vice versa in Mammoth, given the values in the columns match. For example, you can join a numeric column with say a value ‘25’ and a text column with the same value ‘25’, without converting the text column to numeric datatype first. Mammoth casts the values to the appropriate type for the join.
Join Types¶
Left Join¶
This option selects all the rows from the current View along with the matching rows from the current view.
![]()
Fig. 109 Left Join¶
Right Join¶
This option selects all the rows from the other View along with the matching rows from the current View based on the join key.
![]()
Fig. 110 Right Join¶
Restrictions¶
Mammoth does not allow Join rules of cyclic manner, i.e. If View-1 has a Join rule with View-2, then View-2 cannot have a Join rule with View-1. (See Fig. 113). It creates a chain of data updates between the Views.
![]()
Fig. 113 If view-1 has a join with view-2 then view-2 cannot join with view-1.¶
The cyclic behaviour can also occur when more than two Views are involved. (See Fig. 114)
![]()
Fig. 114 If view-1 has join with view-3 and view-3 has a join with view-2 then view-2 cannot join with view-1.¶