Find value in unrelated Table based on value

[johngouws]

Hi. 

I am sitting with a scenario where I need to show a % value from 1 Table, based on a % value from another table. What makes this 'interesting' is that the 2 table are not associated and the calculation cannot be done in the Load Script, because there is a variable that comes into play in the App front end. The user is able to change the variable. 

In essence what I have is a Data table containing REP, Sales and Target fields. I have a second table, Lookup, which has a list of various Achievement percentages with a Incentive value for each Achievement percentage. 

When I calculate the Achievement percentage (Sale/Target*variable) in the front end, this result needs to look in the Lookup table and return the related Incentive value. The attached App shows what I mean about the Variable the user can use. The script I have is : 

SET v_Company = '.02';

Data:
LOAD * INLINE [
REP, Sales, Target
01, 2500, 2800
02, 4000, 3800
03, 2100, 2700];

Lookup:
LOAD * INLINE [
Achieved_%, Lookup
75%, 5%
76%, 6%
77%, 7%
78%, 8%
79%, 8%
80%, 9%
81%, 9%
82%, 10%
83%, 10%
84%, 10%
85%, 11%
86%, 12%
87%, 13%
88%, 14%
89%, 14%
90%, 15%
91%, 15%
92%, 16%
93%, 16%
94%, 17%
95%, 17%
96%, 18%
97%, 18%
98%, 19%
99%, 19%
100%, 20%
101%, 20%
102%, 20%
103%, 20%
104%, 20%
105%, 20%
105%, 25%
107%, 25%
108%, 25%
109%, 25%
110%, 25%
111%, 25%
112%, 25%];

I greatly appreciate any help or suggestions with this. 

[miskinmaz]

Create another variable with the expression achievement % ((Sale/Target*variable) )
and use this variable in set analysis to filter out the lookup value.

[marcus_sommer]

To fetch the lookup-value you could use an expression like this one:

pick(match(round(Sales / Target * 100), $(=concat(Achieved_%, ','))), $(=concat(Lookup, ','))) / 100

Important is the rounding - maybe with floor/ceil instead of round - of your achivement-calculation so that it matches the values from your island-table. Personally I would probably use unformatted integer-values in this lookup-table to avoid any formatting-issues and the possible need to handle them as strings which would then require the use of quotes within the $-sign expansion and also additionally efforts to sort the concat to ensure a synchronization of lookup- and return-field.

Further you need to extend this table to all possible values or to apply some logic to fetch the not listed values - maybe with one or two if-loops and/or alt() and a range-function. Maybe something like this:

alt(pick(match(round(rangemin(Sales / Target * 100, 112)),
   $(=concat(Achieved_%, ','))), $(=concat(Lookup, ','))) / 100, 0)

- Marcus

[sunny_talwar · Accepted solution]

Try something like this

num(Aggr(If(Round(Ceil(Sales/Target,0.1,0.1) * (1- $(v_Company))*100)/100 = [Achieved_%], Lookup), [Achieved_%], REP))

[johngouws]

Thank you again Sunny. 

Your solution works 'straight out of the box'.

I do think I will need to play around with the formatting as @marcus_sommer , But the concept is spot-on. 

Thanks again. 

[sunny_talwar]

I agree, my expression is to guide you in the general direction, but you will have to fine tune this based on your requirement. Things such as round, ceil, floor etc will have to be decided to make sure you get to the correct lookup value, but that something you should be able to figure out based on the expression.

[johngouws]

Hi Marcus, 

When I have some time I am going to try out your solution as well. 

Thank you.