Vlookup in Alteryx Best Practice?

[jdelaguila]

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?
CONTRIBID	NAME	AMOUNT	RANGE
123	RYAN JAMES	5	$5-$9.99
123	RYAN JAMES	10	$10-$14.99
123	RYAN JAMES	100	$100-$249.99
123	RYAN JAMES	5000	$5,000-$9,999.99
234	ANITA SMITH	1.99	$0.01-$4.99
234	ANITA SMITH	45	$35-$49.99
234	ANITA SMITH	299	$250-$499.99
234	ANITA SMITH	600	$500-$749.99
456	DONNA ELLET	1500	$1,000-$1,999.99
456	DONNA ELLET	74	$50-$74.99
456	DONNA ELLET	129	$100-$249.99
456	DONNA ELLET	400	$250-$499.99

CODE	DESC
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+
X	Neg Amount
Z	Unknown

[RobertOdera · Accepted solution]

@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 · Accepted solution]

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 · Accepted solution]

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

[jdelaguila]

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

[RobertOdera]

@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.

[RobertOdera]

@jdelaguila 

You're most welcome!

[pirroav]

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]

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.