Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Partner
Partner

Find value in unrelated Table based on value

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. 

 

 

 

 

 

 

 

1 Solution

Accepted Solutions

Re: Find value in unrelated Table based on value

Try something like this

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

6 Replies
Highlighted
miskinmaz
Contributor II

Re: Find value in unrelated Table based on value

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

Re: Find value in unrelated Table based on value

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

Re: Find value in unrelated Table based on value

Try something like this

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

Partner
Partner

Re: Find value in unrelated Table based on value

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. 

 

 

Re: Find value in unrelated Table based on value

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.

Partner
Partner

Re: Find value in unrelated Table based on value

Hi Marcus, 

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

Thank you.