Join or Merge¶
Join or Merge task is used to link data with another view based on a column shared between these two views.
This is similar to JOIN operation in an SQL database. It is also similar to doing a VLOOKUP in Microsoft Excel.
Let us start with the following two views.
- Customers → View 1:
- Orders - View 1
Let us associate the customer name with the orders. Note that the customer ID column has corresponding values in Customer → view 1.
In the view Orders - View 1 do the following:
- Open Data Preparation menu and click on Merge & Branch Out.
- Select Join.
- Select the view 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.
The following table displays your final result:
The following options are supported in this task:
- View to Join with: Allows selecting a view to join with.
- Common keys: Allows mapping between pairs of columns with common values.
- Join Type: Allows choosing of one of these four join types: Left, Right, Inner, Outer.
- Mammoth does not allow certain type of joins. For more information, see Restrictions .
This option selects all the rows from the current view along with the matching rows from the current view.
This option selects all the rows from the other view along with the matching rows from the current view based on the join key.
This option selects only matching rows from the current view and the other view where the join condition is met (If at all).
This option selects all the rows from the current view and the other view, regardless of whether the join condition is met or not.”
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. 29). It creates a chain of data updates between the views.
The cyclic behaviour can also occur even when more than two views are involved.(See Fig. 30)