Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

VLOOKUP help

lancegoh1
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

 

 

5 REPLIES 5
cmcclellan
13 - Pulsar

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

cpituley
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

ponraj
13 - Pulsar

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

 

workflow.PNGResults.PNG

lancegoh1
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

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
Labels