topic Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!) in QlikView

Interpret Field Values as FORMULAS in a chart (without using pick func.!)

ivan_will — Wed, 20 May 2015 11:01:08 GMT

Hello all,

I have the following set:

Till now I am using: Pick( Match(KBI, $(vFormulaKBIs) ),$(vExt_Formula) )

where in the $(vFormulaKBIs) I have concatenated all KBIs' names and in $(vExt_Formula) all corresponding formulas from the Complex Formula field.

The above method works BUT for each KBI it calculates all the expressions and this decrease the performance in times.

(I have another KBIs which are not calculated by this formula but use rangesum between both formulas). And as you know if you use pick and if some of the expressions is wrong it will destroy all your reports as it will return error.

So what I want to have now is to to interpret the Complex Formula for each row where it is needed!

I have tried a lot of different ways but didn't managed to make it working!? Fieldvalue, fieldindex, ....

If someone has an idea I will be grateful!

Thanks

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

tresB — Wed, 20 May 2015 11:23:15 GMT

The above method works BUT for each KBI it calculates all the expressions and this decrease the performance in times.

(I have another KBIs which are not calculated by this formula but use rangesum between both formulas). And as you know if you use pick and if some of the expressions is wrong it will destroy all your reports as it will return error.

I can think of this Pick(Match(... approach to be suitable one. And didn't quite agree with your opinion highlighted above (if I have not misuderstood your point).

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

ivan_will — Wed, 20 May 2015 11:27:37 GMT

I have tested it a lot of times and believe me it is performance killer when you have about 50 kbis with a very complex formulas.

That's why I am looking for a better solution.

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

tresB — Wed, 20 May 2015 11:32:55 GMT

I agree, and that is very obvious specially when you have complex expressions with aggr() and so.. Hence, it is possibly not something to do with pick(match.. but the expressions themselves. For optimization, you might have to take some calculations into the script.

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

ivan_will — Wed, 20 May 2015 11:37:43 GMT

thanks but I am looking for non-obvious solutions. I have tested the obvious ones already!

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

ivan_will — Wed, 20 May 2015 13:55:47 GMT

Any ideas? ...

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

ivan_will — Thu, 21 May 2015 06:13:58 GMT

Any ideas? ...

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

ivan_will — Fri, 22 May 2015 06:01:54 GMT

? ... is it possible ?

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

tresB — Fri, 22 May 2015 06:08:26 GMT

When I re-read your question, I once stopped at your post subject. Are you thinking of putting the expressions in a field and try to use them in the front-end? If yes, yes that is possible. Could you post a sample qvw with sample data where we may work upon ?

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

ivan_will — Fri, 22 May 2015 07:10:16 GMT

Hi, here is a test app with the current solution.

I as have said before I do not want to use pick function!

Thanks!

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

tresB — Fri, 22 May 2015 09:07:57 GMT

I am afraid that, this might not be possible without pick(). Had there been an equivalent function of evaluate() to be used in the front-end, could help here. However, $ sign expansion is used in qlikview for nearly similar purpose. But it could not help here because of it's limitation, that $ expansions are performed outside chart and before it's data is processed and outside the context of the dimensions of the chart. so expected similar expression something like $(=[Complex Formula]) would not work beacuse of multiple possible values at the time of $ expansion. Note, if you select a single value from the formula field making it single possible value, you would get the result right.

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

ivan_will — Fri, 22 May 2015 09:12:46 GMT

I know all of the above mentioned by you ... as said I am looking for non obvious solutions ...
Thanks anyway!

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

Anonymous — Tue, 26 Jan 2016 11:15:15 GMT

Hi ! did you manage to solve it? i have the same problem....

Regards

Dai

Re: Interpret Field Values as FORMULAS in a chart (without using pick func.!)

ivan_will — Tue, 26 Jan 2016 18:00:24 GMT

There is no other way except IF statements or pick(match ...

If possible use IF as pick match may cause you some performance issues

Best