topic Re: pick rating on the fly using calculated score in QlikView

pick rating on the fly using calculated score

Anonymous — Mon, 31 Mar 2014 17:59:02 GMT

hi guys, looking for some directions here.

I have large QVD which represents financial transactions - 90 million rows and growing. based on some KPIs and complex calculations, I determine overall score for a specific entity. The score is a number from 0 to 100 and it CANNOT be calculated in a load script - depending on user selections, the scores would be different.

with that said, here is my challenge. I am a given a little table that determines a rate for an entity, based on its score - basically that table has low score and high score and a rate from 1 to 5. I need to calculate the score (again on the fly based on user selections) and using that table identify its rate.

Here is a few options I considered:

1) create a data island table for scores/rates and use if thens to pick a rate from there. While it works great on by 10,000 records sample, it is taking forever in my real app.

2) use aggr, set analysis and advanced search expression in it - did not work because aggr is calculated not per row but per chart.

Any other ideas?

Re: pick rating on the fly using calculated score

Anonymous — Mon, 31 Mar 2014 18:59:07 GMT

What is the relation between score and rate? Can it be something like

0 to 20 1

21 to 40 2

...

If yes, you can try this.

rate = div(score-1,20)+1

If not, maybe enclosed if()

Re: pick rating on the fly using calculated score

Anonymous — Mon, 31 Mar 2014 19:02:45 GMT

thanks for your reply, Michael. the scores are decimal numbers like 87.45 and rates are defined as ranges like 0..56.25 for rate 1, 56.26..63.78 for rate 2 etc. I have to use a lookup table because of that.

rate is determined by statistical calculations on a side - another team is doing that using special software

Re: pick rating on the fly using calculated score

Anonymous — Mon, 31 Mar 2014 19:17:48 GMT

Ok, what about "if":

if(score<=56.25, 1,

if(score<=63.78, 2,

if(score<=xx.xx, 3,

if(score<=yy.yy, 4, 5

))))

I expect it to be slow but hopefully better than with the data island. You can give it a try on a limited data set and compare.

Re: pick rating on the fly using calculated score

Anonymous — Mon, 31 Mar 2014 19:39:24 GMT

I cannot hard code these numbers as they will be updated once a month (that other department will recalculate the numbers and send them to me) but you gave me an idea to create this if-then expression in my load script and then use as dynamic expression (dollar expansion) in the actual chart objects.

I am a bit pessimistic though due to the volume of data I have but you gave me hope

is this the only way? I understand QV needs to operate with data loaded in data model and it is no SQL when you can join tables on the fly, but there must be a better solution to that.

Re: pick rating on the fly using calculated score

Anonymous — Thu, 03 Apr 2014 15:19:51 GMT

this did not work as i was hoping. while i was able to build if-then statements dynamically in my load script, i also need to use one variable dimension. I used dollar sign expansion but this only work if I manually select dimension value. Apparently as I've just read on a forum, dollar sign expansion is done for the whole chart not per dimension row so this did not work for me.

I am back using data island table but it is terrible slow.

Any other expert opinions? I could do that in Excel in 1 minute using vlookup function and I guess was hoping QV can do something like that on the fly.

Re: pick rating on the fly using calculated score

Anonymous — Fri, 04 Apr 2014 18:21:18 GMT

(I'm back after a few days "off-line".)
I think there should be a better way. For now, a sugestion. Instead of pre-building the whole "if" expression in the script, define only the "border" values as variables in the script, in this case it will be something like this:
vOne = 56.25
vTwo = 63.78
vThree = xx.xx
vFour = yy.yy

And the expression will be:
if(score<=vOne, 1,
     if(score<=vTwo, 2,
     if(score<=vThree, 3,
     if(score<=vFour, 4, 5
))))

Maybe it helps...

Re: pick rating on the fly using calculated score

Anonymous — Fri, 04 Apr 2014 18:51:18 GMT

thanks for trying help me but it would not work in my case. I have 8 metrics that i need to score/rate and each one actually has a rate from 1 to 100. i would need to create 800 variables

so far the best solution i found is to use firstsortedvalue with nested aggr - this actually work well but terribly slow. takes 50 seconds to calculate one chart on a very powerful server.

I gave up on if-s idea because dollar expansions do not work on expressions within charts - expression is expanded before chart is calculated hence you cannot refer to chart dimensions.

Re: pick rating on the fly using calculated score

Fri, 04 Apr 2014 22:04:38 GMT

Borys,

interesting case

I have no license, so i cant open qvd (during weekend).

Please, give me in xls your surce tables with sample data, Script, maybe some example of expected result in xls...

Give me some screen's, sample expressions, etc.

Especially show me (eplain me) how variable dimension works in your app

(you told: this did not work as i was hoping. while i was able to build if-then statements dynamically in my load script, i also need to use one variable dimension. I used dollar sign expansion but this only work if I manually select dimension value. Apparently as I've just read on a forum, dollar sign expansion is done for the whole chart not per dimension row so this did not work for me.).

I will also try to help

regards

Darek

Re: pick rating on the fly using calculated score

Sun, 06 Apr 2014 10:15:25 GMT

Borys,

i thought a little about your case.

For me this "second step" of your calculation (determining rate value) should be done as a expression.

If you let me know your data model, your chart dimensions, structure of your rating data (you told, that your rating dictionary has 800 points, you have 8 metrics etc) I'am sure we will find a way how to generate "score to rate transformation function" to have good performance and flexibility

regards

Darek.

Re: Re: pick rating on the fly using calculated score

Mon, 07 Apr 2014 15:08:11 GMT

Borys,

i prepared small example in assumption, that you have to get your rating regarding to some dimension value ....

For example purposes i assumed, that i have some data and my score function is simple avg.

I added field Dim_1 to simulate some user selections.

In my opinion this aproach should not impact a lot your applicationperformance

Let me know if it helped you

If you like this direction, i have some additional idea, how to optimize it

Regards

Darek

Re: Re: pick rating on the fly using calculated score

Anonymous — Mon, 07 Apr 2014 16:40:42 GMT

dziękuję, Darek! you loved my little challenge, did not you?

I think this is a great solution - if i got it right, you build 7 different expressions in the load script, then use pick function to pick one of the 7 for the corresponding dimension.

I tried and it is very fast - much better than mine firstsortedvalue / aggr solution. I think it is definitely a great way to address a problem - but unfortunately not going to work for me.

My requirements have just changed and there will be 112 different dimension values to rate - not 7 as i thought initially. And every one of these 112 will be using different from/to scores and ratings 1..100, so we are looking at 112 expressions in that pick function. Every one of those 112 expressions will have approximately 7000 characters in length (all if/then-s for every rate 1 to 100), so the total length of my pick expression would be around 784,000 characters. I've read somewhere that QV will start crashing on you once you approach expression length in 20,000-50,000.

My solution with firstsortedvalue / aggr actually worked but it was super slow - like 60 seconds to recalculate my chart.

But after much thinking I looked at it from another perspective - I changed my source data for that rate table.

Basically the idea is to load scores and rates in a horizontal table like that (like a cross table):

targetdim, score_from_rate1, score_from_rate2, ... score_from_rate100

this table will have 112 rows.

Then in a chart, use expression like this one:

IF ( $(mymetric) < ONLY ( score_from_rate1), 'rate 1'

,IF ( $(mymetric) < ONLY ( score_from_rate2), 'rate 2'

...

,IF ( $(mymetric) < ONLY ( score_from_rate100), 'rate 99'

,IF ( $(mymetric) < ONLY ( score_from_over_rate100), 'rate 100'

)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) //close all 101 if-s

this works pretty fast - takes 4-5 seconds to recalculate my chart.

I am still not 100% happy as it was such a hassle and still looks ugly but it works.

I am going to accept your answer as correct one though - thanks for trying solve this!

Re: Re: Re: pick rating on the fly using calculated score

Mon, 07 Apr 2014 17:13:03 GMT

Borys,

proszę

i think you may find something interesting in v1

So, aggregate formula for each dimension value as a row in formula table

Then using dimension number, with pick you may ask $(=fieldvalue('field',nbr))

Remember, that it will work nice while field has distinct values. So, it will be better to check it during reload. If 2 dimension values have the same score ranges and rating, you will have to have the same formula and dim number).

Your solution with 100 columns and 112 rows (associated with dimension) looks nice. But i think, that there will be better if you preaggregate data stronger. This may be also more complex script if you would like to prepare it generic (assuming, that number of your ratings /columns in your solution/ will grow up). There is of course generic load, but ....

We may also go into table with 112 columns: formula_1.formula_2, .... formula_112 and 1 row. Then pick(nbr, $(=only(formula_1)), ...,$(=only(formula_112))). It is also "generic" and may be easy prepared in the script.

poka

Darek

Re: Re: pick rating on the fly using calculated score

Mon, 07 Apr 2014 19:42:47 GMT

Borys,

1 more idea with your 100+1 columns and 112 rows table.

Columns are:

Dim, r1,r2,r3, ... , r100

In rn column you have n'th range from value.

Check how fast is expression:

-RangeSum(score>=r1,score>=r2,score>=r3,.......,score>=r100)

Re: Re: pick rating on the fly using calculated score

Anonymous — Mon, 07 Apr 2014 19:55:58 GMT

got it, very cool

Re: Re: pick rating on the fly using calculated score

Anonymous — Mon, 07 Apr 2014 19:57:21 GMT

sorry, Darek, not sure I understand this. RangeSum will give a sum of all boolean expressions and then what can i do with that?

Re: Re: pick rating on the fly using calculated score

Mon, 07 Apr 2014 20:06:08 GMT

Look,

in QV true=-1, false=0

simple example:

DIM,range,score_from,score_to

A,1, 0, 0.25

A,2, 0.26, 2.55

A,3, 2.56. 7.80

A,4, 7.81

our table:

DIM, r1, r2, r3, r4

A, 0, 0.26 , 2.56, 7.81

lets test:

score = 3

-rangesum(3>0, 3>0.26, 3>2.56, 3>7.81) = - rangesum(-1,-1,-1,0)=3

another test:

score=2.3

range=-rangesum(2.3>0,2.3>0.26,2.3>2.56, 2.3>7.81)=-rangesum(-1,-1,0,0)=2

Re: Re: pick rating on the fly using calculated score

Anonymous — Mon, 07 Apr 2014 20:45:59 GMT

this is a very interesting idea - I will think about it more tonight. Thanks for your help again!

Re: Re: pick rating on the fly using calculated score

Mon, 07 Apr 2014 20:52:32 GMT

Your range should be equal to number of score_from, which are less than calculated score.

Anyway, i'am not sure if this will be good for performence. It is always 11200 comparisions.

I think, that it may be better to prepare something like tree.

Let's asume range table (for one Dim value):

range, score_from, score_to

1, 0 , 5.05

2, 5.06 , 10.01

3, 10.02 , 25.04

4, 25.05 , 30.20

5, 30.21 , 50,54

.....

10, 101,33 , 110,43

11, 110,44 , 137,01

.....

20, 189.01 , 205,45

......

100, 567,99 ,

We may prepare 2 levels if (like tree)

if(score<=110.43, if(score>=0,1,if(score>=5.06,2,if(score>=10.02,3,if(score>=25.05,4,.......))))))))),if(score<=189.01,if(score>=110,44,11,........)))))))))),..........)))))))))))))))))))))))))))

So, first level looks in which tenth is result. 2'nd level checks only values in tenth found in 1'st level.

This way, if you have 100 ranges and 112 dim values, max number of comparisions will be 10x112 (to find tenth for each dim value)+10x112(to find range for each dim value) = 2240, and no summation needed.

With rangesum we have always 100x112 logical comparisions and next 112 summations.

regards

Darek

Re: pick rating on the fly using calculated score

Anonymous — Mon, 19 May 2014 01:27:48 GMT

unfortunately performance is left to be desired so I will be open to any other suggestions.

It is one of these things which are very easy to do in Excel using vlookup function but QV cannot handle this well. I have hard time to explain this to my project stakeholder who happens to be an Excel guru