Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!

Join: how to keep every left row, not just the matched rows

Highlighted
5 - Atom

I am trying to join sheet A (4395 rows) and sheet B (4824 rows) together, the alterlyx join only returns all matched rows, in this case the result is 3716 rows. What I want is return everything in sheet A, so the result should be 4395 rows, just like the excel vlookup. Anyone knows how to do this?

Highlighted
Alteryx
Alteryx

Hey @xxyb ,

 

If you are looking to also include the unmatched rows from the right data you can connect the J and R output anchors into a union tool! 

 

Sorry realized I wrote for Right and you meant Left. Same logic would apply. Thats a common use case of our clients who want to simulate a SQL "Left Outer Join" or a "Right Outer Join"

 

This can be configured to stack the data in the order that you want, in the order it is, or by column name.

 

Best,
Peter

Highlighted
5 - Atom

I just tried but the row numbers it returns is matching the right sheet, not the left one. I tried to do a filter (primary key is not null) after the union but this returns the same result as join. So it still doesn't work as expected.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi,

 

To keep all values from sheet A and the joined values from sheet B you would need to do a left outer join, this could be done by adding a union tool after your join and unioning the "left" (L) and "joined" (J) anchors together as seen below.

 

I've attached my workflow for you to download if needed. If this solves your issue please mark it as correct, if not let me know!

clipboard_image_0.png

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Could you attach your current workflow?

Labels