ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Vlookup in Alteryx Best Practice?

jdelaguila
8 - Asteroid

Hi Alteryx Community,

 

First of all thank you so much for answering my questions so quickly. You all have been great!

 

I have a "Best Practice" question ref. vlookups. When i used to work in Access or Excel we did Vlookups and or did a Match back to a table to obtain and update a new field. 

 

In the example below i have a bunch of names who donated a certain amount. I used to link to the table below that,to create the field called RANGE.

 

Is there a way to link back to a table or is the best practice to just hard code it all in the Formula Tool? Thanks for any insight you can provide. Javier

 

   Whats the best way to create this field?
CONTRIBIDNAMEAMOUNTRANGE
123RYAN JAMES5$5-$9.99
123RYAN JAMES10$10-$14.99
123RYAN JAMES100$100-$249.99
123RYAN JAMES5000$5,000-$9,999.99
234ANITA SMITH1.99$0.01-$4.99
234ANITA SMITH45$35-$49.99
234ANITA SMITH299$250-$499.99
234ANITA SMITH600$500-$749.99
456DONNA ELLET1500$1,000-$1,999.99
456DONNA ELLET74$50-$74.99
456DONNA ELLET129$100-$249.99
456DONNA ELLET400$250-$499.99

 

CODEDESC
A$0.01-$4.99
B$5-$9.99
C$10-$14.99
D$15-$24.99
E$25-$34.99
F$35-$49.99
G$50-$74.99
H$75-$99.99
I$100-$249.99
J$250-$499.99
K$500-$749.99
L$750-$999.99
M$1,000-$1,999.99
N$2,000-$4,999.99
O$5,000-$9,999.99
P$10,000+
XNeg Amount
ZUnknown
RobertOdera
12 - Quasar

@jdelaguila 

 

Using a table input or a text input would likely require you to assign DESC to every unit increment of the Amount that you want to evaluate. So, if 1 then, if 2 then, etc., I don't recommend it.

 

Use Alteryx to bend the data (to do your heavy lifting). I would use the Formula Tool like so:

 

IF IsEmpty([Amount]) THEN 'Unknown'

ELSEIF [Amount] >= 10000 THEN '$10,000+'

ELSEIF [Amount] > 4999.999 THEN '$5,000-$9,999.99'

ELSEIF [Amount] > 1999.999 THEN '$2,000-$4,999.99'

ELSEIF [Amount] > 999.999 THEN '$1,000-$1,999.99'

ELSEIF [Amount] > 749.999 THEN '$750-$999.99'

ELSEIF [Amount] > 499.999 THEN '$500-$749.99'

ELSEIF [Amount] > 249.999 THEN '$250-$499.99'

ELSEIF [Amount] > 99.999 THEN '$100-$249.99'

ELSEIF [Amount] > 74.999 THEN '$75-$99.99'

ELSEIF [Amount] > 49.999 THEN '$50-$74.99'

ELSEIF [Amount] > 34.999 THEN '$35-$49.99'

ELSEIF [Amount] > 24.999 THEN '$25-34.99'

ELSEIF [Amount] > 14.999 THEN '$15-$24.99'

ELSEIF [Amount] > 9.999 THEN '$10-$14.99'

ELSEIF [Amount] > 4.999 THEN '$5-$9.99'

ELSEIF [Amount] >= 0 THEN '$0-$4.99'

ELSEIF [Amount] <0 THEN 'Neg Amount'

ELSE 'Fix Me'

ENDIF

 

For this type of hard-code, I always insert a Fix Me clause just in case something falls through the cracks (so it can stick out and get resolved ASAP). I hope this helps.

 

RolandSchubert
15 - Aurora
15 - Aurora

Hi @jdelaguila ,

 

for the ranges with code A-P it should be possible to split the ranges to upper and lower bound, use an Append Fields tool to append the range boundaries to the other dataset and use a formula to select the "right" range. It's more flexible than hardcoding the ranges, but does not cover the "unknown" case.

 

I've attached a sample workflow, hope, this is helpful.

 

Best,

 

Roland 

neilgallen
12 - Quasar

Throwing in my two cents here, just because I feel the tile tool doesn't get enough love.

 

The the tile tool lets you set manual breakpoints and this can be used to identify each row. This setup doesn't require as long of a formula or create additional records, which could be useful if your actual dataset is quite large. As many things are in alteryx, there are 900 ways to skin a cat

ada01dd4ca0a83a063a68a0d79cc69eaf313f81f72326e46fc88e7786a5ea54c

 

Annotation 2020-04-14 081310.png

jdelaguila
8 - Asteroid

Thank you everyone! These are all great ideas and solved my answer! Can't thank the community enough.  Javier

RobertOdera
12 - Quasar

@RolandSchubert 

 

Nice, using an append tool versus a static text file with values for each incremental within lower and upper bounds!

I added treatment to your formula to enable your approach to address the unknown case.

 

I added two test lines in the input and a Range2 calculation in the formula tool after the append (insert the highlighted lines in your formula, the second highlight is redundant as I now see the $10,000+ already taken care of in your original post).

Should now be good-to-go re @jdelaguila original use case.

 

For_Roland1.PNGFor_Roland2.PNG

RobertOdera
12 - Quasar

@jdelaguila 

You're most welcome!

pirroav
6 - Meteoroid

Hey, 

 

If you want to almost exactly duplicate the functionality of a VLOOKUP, use the Find Replace tool. It will add a new column without duplicating any records if you select the "Append Fields" option. 

 

Hope this helps! 

ImadZidan
12 - Quasar

Hello @jdelaguila ,

 

Hard coding is never a good practice. I believe this should be tackled by design.

For example I would split CODE, DESCRIPTION table to something like

CODE FROM TO , which will capture the range. I will then check the Amount against FROM TO range.

 

The final step would be formatting the number.

 

I hope this makes sense.

 

Labels