topic Re: How to make dynamic expressions work? in QlikView

How to make dynamic expressions work?

Thu, 12 Dec 2013 12:14:56 GMT

Hello QlikCommunity,

I need some advice on how I can best solve my problem.

I have a long list of KPI's (lets say 100) and I want to present these in a chart (lets say a straight table). On the rows, the KPI's are listed, and in the columns the value and target of the KPI.

The problem however is, that the expression of.....
- KPI nr 1 could be an expression with sum (e.g. sum(value) )
- KPI nr 2 could be an expression with avg (e.g. avg(value) )
- KPI nr 3 could be an expression with count (e.g. count(value) )

What I thought that could work, is load my expression (like the ones mentioned above) along with my KPI-definitions, as a separate column in my datamodel, so I can use this (loaded) expression in my chart to calculate the sum, average or count when needed (of course all in 1 and the same column).

I don't get it working! So my questions are:
[1] Who can make this work? (and help me out how)
[2] If the answer on [1] == nobody, please answer if this indeed is not possible

Attached I have an example application, where I have loaded my expressionsin the loadscript, and tried to make it work (but the column indicates only "-" (null).

What the outcome _should_ be is also listed, because an alternative approache _does_ work (the nested if statement)

pseudo:
- if (KPInr = 1;sum(value);avg(value))

But If I implement this nested if statement for about 100 KPI's,
my code looks like spaghetti
I'm afraid that the performance may not be good

Hope you have some advice for me!

The .qvw also is self-explanatory IMO but feel free to ask additional info when things are unclear.

Roberto

PS: other approaches are also welcome, as long as it is a (good) alternative for the nestedIf (100x)

Re: How to make dynamic expressions work?

swuehl — Thu, 12 Dec 2013 14:23:58 GMT

I believe it won't work like you want it to.

You can only define a single expression per chart, you can't change the expression for each dimension value (i.e. create an expression based on current dimension value, e.g. using dollar sign expansions).

You can create a single expression that checks the dimension value, then switch to an expression to use accordingly (like you've done with your nested if()'s, or similar using pick/ match combination).

Re: How to make dynamic expressions work?

Nicole-Smith — Thu, 12 Dec 2013 15:00:09 GMT

To get this to work, I added some load script:

Then on the straight table, I just made the dimension [KPI naam] and the expression is $(vMetrics).

See the attached to see it in action.

Re: How to make dynamic expressions work?

swuehl — Thu, 12 Dec 2013 15:05:50 GMT

Yes, Nicole, that's a single expression with the mentioned pick/match combination (similar to the nested if() statements Roberto is using).

It's not what I think he is referring to as a 'dynamic expression' (which I believe is not feasable).

Re: How to make dynamic expressions work?

simondachstr — Thu, 12 Dec 2013 15:09:06 GMT

Have a look at the ValueList(), ValueLoop() functions to create dynamic dimensions. Maybe you'll come up with an alternative displaying your KPI using those.

Re: How to make dynamic expressions work?

Nicole-Smith — Thu, 12 Dec 2013 15:09:18 GMT

You're correct--it's not feasible loading it in from a file. I just thought I'd give him something to go off of that actually works, since it's sometimes hard to understand without seeing an example

Re: How to make dynamic expressions work?

swuehl — Thu, 12 Dec 2013 15:14:56 GMT

To be clear, I am not saying anything against your proposed solution, that's what I also had in mind.

I am just saying, something like this might be a work around for what Roberto was trying to achieve with something I believe just won't work.

Re: How to make dynamic expressions work?

Thu, 12 Dec 2013 15:44:51 GMT

Nicole and others, Thanks for all the (quick!) answers!

Nicole's approach works in my small project. I wonder: can we say something on performance? would one of the mentioned approaches be preferred (and why)?

Current options are

- nested if

- this pick-match approach

- the valueloop / valuelist (which I find a bit hard to understand; I'll try to find out later...)

It would be great if we can say something on what would be the best approach. Thanks!

Re: How to make dynamic expressions work?

Nicole-Smith — Thu, 12 Dec 2013 15:49:26 GMT

You can find some performance info on the pick-match approach here: http://community.qlik.com/message/286947#286947

I've always considered pick-match to be better than nested ifs, but someone can correct me if I'm wrong.

If you're using valueloop/valuelist, you will need nested ifs or pick-match anyway, which just adds more complexity to your solution.

Re: How to make dynamic expressions work?

Thu, 12 Dec 2013 15:58:36 GMT

Thanks a lot!

Re: How to make dynamic expressions work?

swuehl — Thu, 12 Dec 2013 16:24:14 GMT

As a first optimization, you can skip the match() and just use the KPI number:

Let vMetrics = 'Pick([KPI nr],' & peek('ConcatExpression') & ')';

I am not 100% sure how much better a pick() (/match() ) perform compared to nested if() statements, at least IMHO it's better to read and maintain.

Re: How to make dynamic expressions work?

veidlburkhard — Fri, 13 Dec 2013 15:59:33 GMT

Hi,

why don't you use this simple solution and take Column(2) as your expression instead of $(=expressie).

This gives the same result as your nested if and should perform 100 times better.

Please try

Regards

Burkhard

Re: How to make dynamic expressions work?

Fri, 13 Dec 2013 16:08:11 GMT

Hi Burkhard,

I doubt if you understood my problem right!? Column(2) refers to the nested-if-expression (or do I misunderstand you?).

If not: I want to get rid of the nested-if-construction and have another way of calculating.

Column(2) is IMO not a solution, since that REFERS to a method (like the nested if method or the pick(match) method or some other...

If I didn't understand you correctly can you share your .qvw and explain why it would be better / faster?

Re: How to make dynamic expressions work?

swuehl — Fri, 13 Dec 2013 16:08:26 GMT

It's giving the same result as the nested if() statements, because it's just referencing the nested if() expression.

Try your chart with the nested if() expression removed.

Re: How to make dynamic expressions work?

veidlburkhard — Fri, 13 Dec 2013 16:34:51 GMT

Sorry, swuehl,

this was too simple...

Re: How to make dynamic expressions work?

Sat, 14 Dec 2013 13:11:09 GMT

No problem. I'm already happy with all answers so far.