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

Assigning rate % and Vlookup in tier calculation

DiyaR
8 - Asteroid

Hi,

 

I am trying to figure out Rate column based on the tier table below..  I was trying to do with Append/Join along with formula but failing as it gives me multiple row output. I am sure there is a way but I am not thinking in right direction or new way of doing it for tier calc along with V lookup for Rule 

 

Tier table

RuleFromToRate
A2                 -         50,000.001.35%
A2    50,001.00     100,000.000.90%
A2 100,001.00     200,000.000.50%
A2 200,001.00     500,000.000.40%
A2 500,001.00 1,000,000.000.10%
C3                 -       100,000.001.15%
C3 100,001.00     250,000.001.00%
C3 250,001.00 1,000,000.000.35%

 

Claim table

AcctClaimedRuleRate should be
Bell    60,000.00A2My first 50,000 should be @ 1.35% and 10,000 @ 90%
Bell    80,000.00A20.90%
Joe 252,000.00C3it will be combination of 1.15% and 1%

 

Thank you and much appreciated 🙂

4 REPLIES 4
ImadZidan
12 - Quasar

Hello @DiyaR ,

 

I had to clean up the data manually for now.

 

this will give you an idea.

 

hope it helps

Greg_Murray
12 - Quasar

@DiyaR,

 

If I understand the question, you just need a join and a couple of filters to figure out if the claimed amount falls within the tiers. From there you can us a formula to figure out what portion falls within each tier. 

Greg_Murray_0-1597757597327.png

 

DiyaR
8 - Asteroid

Sorry, I was not great in explaining... But yes, I wanted the claim to fall in the tier. Thank you and much appreciated 🙂

DiyaR
8 - Asteroid

Thank you.. I was doing the same thing, however my claim was not falling in the tier.

Labels