Distinct Vlookup in Alteryx For Similar Values

Question

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 1Field 2Field 312datayes13dataaayes12dataano

Input 2 -

Field 1 Field 2Field 312data1yes12data 2 no

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

Field 1Field 2Field 3Field 412datayesno13dataaayes[null]12dataanono

The Output that I desire :

Field 1Field 2Field 3Field 412datayesyes13dataaayes[null]12dataanono

atcodedog05 · Accepted Answer

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 😅

atcodedog05 · Accepted Answer

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 🙂