Join Data in a Wrangle

“The Join Node allows you to join 2 sets of data which share common variables.” Use it to bring two data sources together into one data store, like survey responses and customer details matched on a customer ID.

When to use this

Join data when the information you need is split across two data stores, and both have a variable you can match on, such as a customer number, store code or respondent ID.

Permission Requirements

You need edit access to the wrangle, and access to both source data stores. See Build a wrangle.

---

Choose a join type

Picture two lists: Input 1 (Left) and Input 2 (Right). The join type decides which records make it into the output.

Join Type	What you get	Example: survey responses (left) joined to customer details (right)
Inner (default)	Only records that match on both sides	Responses from customers who are in the customer list
Left Outer	Every left record, plus matching right data where there is some	Every response, with customer details where found
Right Outer	Every right record, plus matching left data where there is some	Every customer, with their response where they gave one
Full Outer	Every record from both sides	Every response and every customer, matched where possible

Where a record has no match, the other side’s fields are left blank.

---

Set up a Join step

1. Add two Readers - One for each data source. See Build a wrangle.
2. Add the Join step - Drag Join from MICROSERVICES onto the canvas.
3. Connect the left input first - Connect the Reader you want as Input 1 (Left) to the Join, then the other Reader as Input 2 (Right). The first one you connect becomes the left input.
4. Choose fields - Click the Join step. On the Field Picker tab, choose fields for the output from Input 1 (Left) and Input 2 (Right).
5. Filter each input if you need to - Use Row Filter For Input 1 (LEFT) and Row Filter For Input 2 (RIGHT).
6. Choose the join - On the Function tab, choose a Join Type.
7. Match the records - Click Add Relationship Rule(s). In Select Common Variable Pair, choose the variable from the left input, click NEXT, then choose the matching variable from the right input. The rule appears in the Lookup Relationship table.
8. Connect the output - Connect the Join to the next step or a Writer.

Tips for matching

• Records match when their values are equal. Matching ignores capitals, but the values must be written the same way: 1.0 doesn’t match 1.
• Multi-response variables can’t be used to match.
• Records with a blank matching value on both sides are treated as a match. Filter out blanks first if you don’t want that.

Use one relationship rule where you can

If you add more than one rule, a record pair is matched when any rule matches, not all of them. A pair that matches on two rules appears twice in the output.

---

Fix duplicate variable names

Both inputs can’t send a variable with the same name to the output. If they do, you’ll see “There is a variable with the same name in both sets of input data. Please change the name of one of these variables in order to proceed.”

1. Select it on one side - On the Field Picker tab, add the variable from one input to Selected Fields.
2. Rename it - Click the pencil icon on its tile. In Edit Variable Name, change the Variable Name (and Variable Label), then click SAVE.
3. Add the other one - Now add the variable from the other input.

Any relationship rules that use the renamed variable are updated for you.

---

Frequently Asked Questions

Which input is left and which is right?

The first Reader you connect to the Join becomes Input 1 (Left), and the second becomes Input 2 (Right). The Field Picker tab shows which is which.

Do I need to select the matching variables as output fields?

No. You can match on any field from each input, whether or not you’ve selected it for the output.

Why are some fields blank in my output?

With a Left Outer, Right Outer or Full Outer join, records without a match on the other side get blanks for that side’s fields.

---

Common Issues and Solutions

”Duplicate Variable”

Both inputs have a variable with the same name in Selected Fields.

1. Rename one - See Fix duplicate variable names.

”Missing join relationship rules.”

The Join doesn’t have a rule to match records on. You’ll see this when you publish.

1. Add a rule - On the Function tab, click Add Relationship Rule(s) and pair a variable from each input.

”Variable with this type is not allowed. Please pick another variable.”

You chose a multi-response variable to match on.

1. Pick another variable - Choose a single-value variable, such as an ID.