Distinct Vlookup in Alteryx For Similar Values

[sanketkatoch05]

Hi, 
After extensively using FInd & Replace tool as my go to option for Vlookup function, I have realised lately that it doesn't work well with similar values. 

For eg : 

Input 1 -

Field 1	Field 2	Field 3
12	data	yes
13	dataaa	yes
12	dataa	no

Input 2 - 

Field 1	Field 2	Field 3
12	data1	yes
12	data 2	no

My Output (Using FIND&REPLACE with Field 1 from both tables as common parameter)

Field 1	Field 2	Field 3	Field 4
12	data	yes	no
13	dataaa	yes	[null]
12	dataa	no	no

The Output that I desire : 

Field 1	Field 2	Field 3	Field 4
12	data	yes	yes
13	dataaa	yes	[null]
12	dataa	no	no

[atcodedog05 · Accepted solution]

Hi @sanketkatoch05 

Since you have 2 keys (Field 1 & Field 3) Find & Replace doesnt support it, for this you need a joint tool. And since we want all rows from left we are doing left join. I using select tool to keep only the required columns.

Hope this helps 🙂 If not let me know how i can help 😅

[sanketkatoch05]

Hi, @atcodedog05.

This works perfectly fine. I was wondering why doesn't FIND & REPLACE work in this case. I have been told in the past that if you have large data, then use F&R tool instead of JOIN, but after looking at this case, I'm afraid whether I'll be using the tool or not in the future. 
But can you guide me why F&R tool is not working in this case, and, what are the advantages of join tool over F&R tool. 

Thanks. 

[atcodedog05 · Accepted solution]

Hi @sanketkatoch05 

It true about the words "if you have large data, then use F&R tool instead of JOIN" Join can create one to many joins leading to data explosion. But F&R has few limitations like it can use only one key field to map and second it will take only first value of the key in the mapping table (i,e key value is yes and is fixed since its a first occurrence). We can modify the workflow for this scenario to use F&R instead of join tool.

Here what i am doing is i am combining Field 1 & Field 3 to create a composite key this way it will be only one key (this method is commonly used in excel vloopups) and implement F&R using those key. Please see the below output.

 Hope this helps 🙂

[sanketkatoch05]

Understood, thank you so much.