When you merge two data feeds, you do so by choosing a column from each that contains the same data in order to align the two. This is your join key.
Are there discrepancies between the data in the data feeds you want to merge? If there are multiple matches or no matches for specific values in your join key, you can align your data using our additional merge options.
This article includes:
- What are the additional options?
- How do I access the additional options?
- Include only the first match
- Include all matches
What are the additional options?
When using the additional merge options, you first select what to do when multiple matches are found for a value in your join key, then you specify which action to take when no matches are found for a value in your join key.
Your selections dictate how the data will be incorporated into your merged data feed.
The following variations are available based on your selections:
If you choose to include only the first match and...
- Exclude rows that do not match
- Include rows for unmatched values from the primary data feed
- Add rows for unmatched values from the secondary data feed
If you choose to include all matches and...
- Exclude rows that do not match
- Include rows for unmatched values from the primary data feed
- Add rows for unmatched values from the secondary data feed
How do I access the additional options?
After selecting data to merge, aligning your data, and selecting columns, you can choose to continue to the additional merge options. To access the additional options, finish making your column selections, then click Next from the Select columns step. (See below.)
This opens the Additional options page. (See below.)
Go here for general information on merging data feeds.
The examples given in this article refer to the merging of these two data feeds:
Include only the first match
In this scenario no new rows are created.
The following options describe what to do when the join key contains multiple instances of the same data and you want to keep only the first match.
For instance, Support Rep Id 1001 in our primary data feed has two matches in our secondary data feed, the entries associated with Kaushi and the entries associated with Seema. In the examples below, Support Rep ID 1001 is only associated with the name Kaushi and entries associated with the name Seema are omitted from the merged data.
Selecting Include only the first match does not add any new rows to your primary data feed.
Option A - Include only the first match and Exclude rows that do not match
For multiple matches, only the first match is kept. If either data feed contains entries that do not match the other data feed, those rows will be omitted. For example, in the table below, the data from line three of the primary data feed and the data from lines two and four of the secondary data feed have been omitted. This is because those lines contain data with no matches in the other data feed.
Option B - Include only the first match and Include rows for unmatched values from the primary data feed
For multiple matches, only the first match is kept. If there is an entry in the primary data feed for which there are no matches in the secondary data feed, the merged data will keep the rows from the primary data feed with blank entries for the unmatched data. For example, in the table below the fourth line of data contains blank entries for Name and Favourite Color.
If the secondary data feed contains entries with no matches in the primary data feed, those rows are omitted. In the example below, the data from line two and line four of the secondary data feed has been omitted.
Option C - Include only the first match and Add rows for unmatched values from the secondary data feed
For multiple matches, only the first match is kept. As in the previous scenario, if the primary data feed does not have a matching entry in the secondary data feed, the merged data will keep the rows from the primary data feed with blank entries for the unmatched data. See the fourth row of data in the example below.
If the secondary data feed does not have a matching entry in the primary data feed, the merged data will keep the rows from the second data feed with blank entries. See the eighth and ninth rows in the example below.
Include all matches
In this scenario new rows are created for each match.
The following options describe what to do when the join key contains multiple instances of the same data and you want to keep all matches. Selecting Include all matches creates a new row for each match. For example, the primary data feed contains two entries for Sales Rep ID 1001 (See above). The first instance is associated with the name Kaushi and the second is associated with the name Seema. Selecting Include all matches results in both displaying in the merged data.
Option A - Include all matches and Exclude rows that do not match
For multiple matches, each match is included. If either data feed contains entries that do not match the other data feed, those rows will be omitted. For example, in the table below, the data from line four of the primary data feed and the data from line two and line four of the secondary data feed has been omitted because those lines contain data with no matches in the other data feed.
Option B - Include all matches and Include rows for unmatched values from the primary data feed
For multiple matches, each match is included. If the primary data feed contains entries that don’t have matches in the secondary data feed, the merged data will keep the unmatched rows from the primary data feed with blank entries. If the secondary data feedcontains entries with no matches in the primary data feed, those rows are omitted. For example, in the table below the fifth line of data contains blank entries for Name and Favourite Color. The data from line two and line four of the secondary data feed has been omitted.
Option C - Include all matches and Add rows for unmatched values from the secondary data feed
For multiple matches, each match is included. As in the previous example, if the primary data feed contains entries that don’t have matches in the secondary data feed, the merged data will keep the unmatched rows from the base data feed with blank entries. You can see this in the fifth line of data in the example below.
If the second data feed contains entries that don’t have matches in the primary data feed, the merged data will keep the unmatched rows from the secondary data feed with blank entries. You can see this in the eleventh and twelfth lines of data in the example below.
