Joining Without Loss of Rows

[klazich]

Hi - absolute beginner over here.

I have two separate Excel files I would like to join together.

In Book1 I have two columns: "Project #" and "Line of Business".

In Book2 I have over 30 columns, among which include "Project #" so this is what I will be using to join the data of the two books.

When I use the Join tool I lose all rows of data that were not matched up to a Project # in my list but I want to match the data as if I was doing a VLOOKUP, only for any errors I'd just get zero/null.

Is there another tool that will match when it finds the Project # and return zero if it does not instead of only outputting data that matched to the "Project #"?

Thanks in advance!

[kelsey_kincaid · Accepted solution]

Hi @klazich - welcome to the joy of Alteryx!

There are a few ways to do what you're attempting. One would be to grab the output anchors from the join and union/stack them all back together.

The other way, and the way I think most beginners find to be the most straightforward is by using the find/replace tool which will return null if no match is found.

[klazich]

Brilliant! Thank you!

[MarqueeCrew]

@klazich 

i have another suggested solution:  use a find Replace tool (join category). Find/Replace project and spend al fields except for project. It will give you null values when it finds no match. You can use a. Multi field formula or data cleanse tool to populate zeros. 

cheers,

 mark