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 - Retrieve corresponding value if amount falls within the range

Joon
7 - Meteor

Hi

 

I have a big fee schedule spanning over multiple products. The maximum tiers differs depending on products. This is what the existing table looks like

 

Table 1 - Fee Table       
ProductTier 1Tier 1 Fee%Tier 2Tier 2 Fee%Tier 3Tier 3 Fee%Tier 4Tier 4 Fee%
A  10,000,0001  20,000,0000.8  30,000,0000.7  
B        10,0000.5        25,0000.45        50,0000.4        75,0000.35

 

The way to read the above table is

E.g for product B

Over 10k to <= 25k : 0.5%

Over 25k to <= 50k : 0.45%

..

over 75k : 0.35%

 

I am trying to retrieve the corresponding tier and fee based on the size of the amount to the following table (i.e. Applicable Tier, Applicable Fee)

Table 2 - Data   
Case IDProductAmountApplicable TierApplicable Fee
2B        50,00130.4
3B        50,00020.45
4B        25,00010.5
6A  20,000,00011
8A  50,000,00030.7

 

Appreciate your advice.

2 REPLIES 2
SamDesk
11 - Bolide

Hi @Joon 

 

So there are a few things to do here:

  • First we need to clean and prep the data so it's in a format where we can join between the two data sources
  • Then we need to apply the logic you've stipulated in determining the right value
  • Finally (not shown here) you would want to reformat your currency values to hold thousand separators (commas) again.

There are a few ways to perform the 'VLookup', it really depends on the volume of your data. An alternative could be to generate a value in the Tier Table for every whole currency number.

 

Annotation 2020-08-26 153418.png

 

Hope this helps!

 

Sam 🙂

Joon
7 - Meteor

Thanks Sam! Your proposed solution helps. I was trying to avoid the other option of generating a value in the Tier Table for every number as I don't think it is very efficient given the long list of products and wide range of amounts.

Labels