Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

VLOOKUP help

Highlighted
7 - Meteor

Hello

 

I'm trying to use the "join" tool to do a vlookup from input 2 and input 1.

under the join tool, for the Left option I put "F5" and then for the Right option I put "a/c number".

I then put a union tool and joined all 3 lines from the join output into the union tool.

when I place the browse tool after the union tool, all my cells appeared [Null], basically everything was blank.

can someone guide me as how do I do a vlookup and create an additional column as my desired output ?

 

Input 1:

F5
123456
654321
123123
321321

333333

 

Input 2:

a/c numberName of Reviewer
123345John
333333mary
321321james
  
  

 

desired Output:

F5Name of Reviewer
123456John
654321 
123123 
321321James
333333mary

 

 

Highlighted
Alteryx Partner

You're after a left outer join, so union only the left and join outputs together.

 

The top stream is simple and doesn't care about the order of the records

 

The bottom stream does the same thing (essentially) but creates a Record ID so you can then put the records back in the same order (because the UNION will put all the lefts first and then the joins)

 

2018-07-04 20_34_09-Alteryx Designer x64 - VLOOKUP demo.yxmd.png

Highlighted
8 - Asteroid

Hi Lancegoh1,

 

I would use the find and replace tool to append here. Give it a shot and let me know how it works for you. Closest exact tool to a VLOOKUP in Excel.

 

Best,

Cameron

Highlighted
13 - Pulsar

You can use multi join tool to get the desired results. 

 

workflow.PNGResults.PNG

Highlighted
7 - Meteor

hi Cmcclellan thanks for your advice. everything seems to be working perfectly, but when I did a total count, i noticed that the total count after I did a union was greater than my original count. wondering why is that so?

 

Regards,

Lance

 

 

alteryx test.png

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@lancegoh1,

 

Here's the thing about a join, for every join key match in the L & R inputs you get the product of L times R records on output.  Whatever your join key is in the vlookup, if you put a UNIQUE tool after your WIP consolidated 4th July.xlsx with that key checked for duplicates, you will find that the count is now 4,232.

 

The vlookup finds the 1st occurrence of the lookup value if multiple are found.  The UNIQUE tool outputs the first occurrence as well.  If there is a preferred record to match, you should SORT the data prior to the UNIQUE tool to put that record first.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels