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?CONTRIBIDNAMEAMOUNTRANGE123RYAN JAMES5$5-$9.99123RYAN JAMES10$10-$14.99123RYAN JAMES100$100-$249.99123RYAN JAMES5000$5,000-$9,999.99234ANITA SMITH1.99$0.01-$4.99234ANITA SMITH45$35-$49.99234ANITA SMITH299$250-$499.99234ANITA SMITH600$500-$749.99456DONNA ELLET1500$1,000-$1,999.99456DONNA ELLET74$50-$74.99456DONNA ELLET129$100-$249.99456DONNA ELLET400$250-$499.99 CODEDESCA$0.01-$4.99B$5-$9.99C$10-$14.99D$15-$24.99E$25-$34.99F$35-$49.99G$50-$74.99H$75-$99.99I$100-$249.99J$250-$499.99K$500-$749.99L$750-$999.99M$1,000-$1,999.99N$2,000-$4,999.99O$5,000-$9,999.99P$10,000+XNeg AmountZUnknown

Vlookup in Alteryx Best Practice?

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

Developer

Developer Tools

Input

Tips and Tricks

Join

In Database

Data Investigation

Database Connection

In-Database

Accepted answers

RobertOdera

@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

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

Vlookup in Alteryx.yxmd

neilgallen

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

Vlookup_in_AYX Best Practice.yxmd

All comments

RobertOdera

@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

Hi @jdelaguila ,

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

Best,

Roland

Vlookup in Alteryx.yxmd

neilgallen

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

ada01dd4ca0a83a063a68a0d79cc69eaf313f81f72326e46fc88e7786a5ea54c

Annotation 2020-04-14 081310.png

Vlookup_in_AYX Best Practice.yxmd

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.

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703