topic Re: Get record value based on expression calculation in QlikView

Get record value based on expression calculation

wim_rijken — Mon, 13 Aug 2018 07:20:27 GMT

I have a chart in where I calculate the SalesAmount per Salesperson.

This is a calculation based on a lot of different sales figures, filtered with set analysis to get the correct result.

It is not doable via scripting, so in my script, I do not have the correct SalesAmount per Salesperson

this is an example of the result of the calculation in the chart, but be aware, this is a calculation of a lot of different sales amounts:

Salesperson	SalesAmount
Dave	5000
Jef	10000
Michael	15000

Now I have a table (Excel spreadsheet) where I upload a "SalesAmount" and "Bonuspercentage".

(so salesamount between 0-2500 = 5%, between 2501-12500 = 10%, between 12501-20000 =15%)

SalesAmount	BonusPercentage
2500	5
12500	10
20000	15

I would like to link them together, so as a final result I would like to see:

Salesperson	SalesAmount	BonusPercentage
Dave	5000	10
Jef	10000	10
Michael	15000	15

Is there a way to get the correct "BonusPercentage" based on the calculated field "SalesAmount" in the chart?

Re: Get record value based on expression calculation

Or — Mon, 13 Aug 2018 08:44:13 GMT

I got a little lost trying to do exactly what you said, so I tried a slight workaround. Instead of using a table with SalesAmount and BonusPercentage, I used a table with BonusStart, BonusEnd, and Bonus Percentage. Hopefully you can make that adjustment in your Excel, but if not, you can use Peek() to do it in your script. There may be a way to get this done with your original data structure but I'm not sure how.

Load * Inline [

Salesperson, SalesAmount

Dave, 5000

Jef, 10000

Michael, 15000

Wile E. Coyote, 1000];

Load * Inline [

BonusStart, BonusEnd, BonusPercentage

0, 2500, 5

2501, 12500, 10

12501, 20000, 15

];

Note that this is a data island - it does not connect to SalesAmount (which you don't want it to). I then used the following expression:

Sum(aggr(if(Sum(SalesAmount)>=BonusStart AND Sum(SalesAmount) <= BonusEnd,BonusPercentage),BonusStart,Salesperson))

Note that this will also work if the bonus table is connected to the sales table via dimensions such as Salesperson:

Load * Inline [

Salesperson, BonusStart, BonusEnd, BonusPercentage

Dave, 0, 2500, 5

Dave, 2501, 12500, 10

Dave, 12501, 20000, 15

Jef, 0, 2500, 11

Jef, 2501, 12500, 22

Jef, 12501, 20000, 33

Michael, 0, 6666, 6

Michael, 6667, 66666, 66

Michael, 66667, 666666, 666

];

Re: Get record value based on expression calculation

BalaBhaskar_Qlik — Mon, 13 Aug 2018 12:32:02 GMT

May be like this:

If(Column(1) >= 0 and Column(1) <= 2500, "5",

if(Column(1) > 2500 and Column(1) <= 12500, "10",

If(Column(1) > 12500 and Column(1) <= 20000, "15")))

Column(1) refers SalesAmount.