Join two data set like VLOOKUP

[Rumanais]

Hello,

I have been trying to join two dataset using Join tool in Alteryx.  I was able to build a workflow (Attached) that would join the data sets and bring values from both data sets where a match is found as well as keep the unmatched records from the first dataset (left join). However, if I also want to keep the duplicate accounts in data set 1 due to some column values being different and creating multiple records of that account, what can be done to this workflow to accomplish that?

I am attaching the WF as well as 2 input files.  Any guidance would be greatly appreciated! 

Thank you.

[Qiu]

@Rumanais 
If I understand correct, there are duplicated records based on "SRC_ACCT_NB2" and NO duplicated records based on "Duplicate Account" in Input 2.
In this case, the way of you are doing by Union the L and J anchor datastream should have the all the records from Input 1.

[Rumanais]

Hi Qiu,

Sorry for delayed response! 

I am looking to keep all of the record (duplicates) from the "SRC_ACCT_NB2" and only bring in records matching from the right join regardless of right dataset had duplicate or unique accounts. My expected output is in the second tab of the data set 1.

I want to mirror the excel VLOOK up with two data sets or Left Join in SQL.

Hope this make sense. 

[CoG · Accepted solution]

I'm not sure I fully understand your requirements, but it seems like you already achieve your desired output after the Union Tool. The Sample Tool is what is causing the output to deviate. I removed the Sample Tool, and cleaned up some of the other tools:

Hope this helps and Happy Solving!

[EdwardCochrane]

To mimic the excel lookup function, you can use the find and replace tool. To perform a left join, you can use the join tool and only use one of the ouutputs.

[Rumanais]

Hi Andrew Merrill,

This is exactly what I was looking for.... thank you so much!!!!