The Merge Route is used to join a primary route with reference data from any other source. Similar, to a join statement in SQL, K3 will use a key from the primary route to identify data in the secondary route and pull all the information into a single route that can then be sent to the Target destination.
The steps to set up a route merge route are below.
1. Create the primary and secondary routes.
The Primary and Secondary routes can have different Source and Target adaptors,. The only requirement is that the primary and secondary route both have columns with data that is matchable, such as an ID or Key field. The key will be used to merge the two routes together. The key used in this example was the Customer Name column, which is identical between the Primary Route and the Secondary Route.
Prerequisite: Both routes need to be setup in K3 before proceeding. We recommend setting both routes to have Deadend targets. That is, this data will flow into K3 but not be distributed anywhere else.
Click here to open the Route guide for step by step instructions on how to create a Route in K3.
Example Primary Route
Example Secondary Route
2. Create a Data Merge Route
Select Data Merge as the Source adaptor, and whichever Target adaptor is needed.
On the following screen, click in the Primary Data Source field to open a drop down menu of available routes. Select the Primary Route from the drop down.
Click int he Secondary Data Source field to open a drop down menu of available routes. Select the Secondary Route from the drop down menu.
After the Primary and Secondary Data Sources are chosen, K3 will create a drop down menu of the columns from each route. Select the column that will serve as the Key in each Key field. Once the Key is chosen, K3 will calculate how many items match between the two routes based on the key.
The percentages displayed below the amount of data that K3 can match based on the key chosen. K3 will merge the routes, even if there is a zero percent match. However a low percent match should act as a warning that the key being used is not a good choice.
If there are multiple null values in one of the routes, K3 will alert the user to this fact, but it is still not indicative of a bad key. Instead a null rate indicates that not all rows have data available.
Next, click Configure Field which will open a window to select the fields that will be brough into the new merged route from each respective route. K3 will indicate if fields are duplicated with red text saying "duplicate field name " located under all fields that have the same name. Click the green toggle button next to one of the duplicate columns to resolve the issue. Please note, the Key column will be duplicated as well, so be sure to click the green button next to one of the columns. This will not affect the merge, but is necessary to continue.
Once all the duplicate fields have been removed, click Looks Good at the bottom right of the window.
Lastly, click Test Merge. A preview of the merged route will appear below. Review the data to ensure it looks accurate based on your confirmation choices above and click Create.
