Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Vlookup inexact

Highlighted
5 - Atom

Hello,

 

I want to do a workflow using something similar like vlook up inexact in excel.

 

I have two databases, one (Base 1) with 28 zip codes and the other one (Base 2) with 30888 zip codes, so, i want to replace the zip codes in Base 1 with the most aproximate zip code of the Base 2.

 

Example:

img 1.jpg

 

I attach a file with the base.

 

I hope you can help me :)

Highlighted
14 - Magnetar
14 - Magnetar

Try something like the attached... by combining both lists (with the source of each list, Base 1 or Base 2, designated in a column) and then sorting by descending area code, you can use a Multi-Row tool to find the nearest match. If there is an exact match, it will use the exact match... if not, it will use the next highest number it finds (which it will carry down from the prior rows).

 

EDIT: Because @MarqueeCrew reads through things better than I do, and pointed out an error in my logic... :) If you want this to behave exactly like it does in Excel, where an approximate match is the previous one not the next one like my original example, then you can sort it by zip code ascending to accomplish the same thing for the closest match prior to the Base 1 zip code being looked up.


(Note: Example workflow attached is in version 11.7)

 

Cheers!

NJ

Highlighted
5 - Atom

Thank you very much for your support, it worked very good! (:

 

Regards (:

Highlighted
8 - Asteroid

 I like this implementation for its simplicity - it still seems very complicated compared to the Excel equivalent.

Highlighted
5 - Atom
Thank you for you help. It really worked!!
Labels