topic Re: Re: Expression optimization in QlikView

Expression optimization

Wed, 11 Jun 2014 15:37:47 GMT

Hello everyone

I'm trying to optimize this expression

if (PER=0,
      IF(IND=0,sum(Value*coef),

           sum(Value*IND*coef)),

                     IF(IND=0,sum(Value*coef*PER),

                                    sum(Value*IND*coef*PER)))

the thing is it depends on the users selection so can't optimize it in the script, and at the same time pick (match) doesn't work because for example PER can aither be 0 or (0 or 1).same for IND.

Any idea can be helpful,

Thanks in advance

Re: Expression optimization

jerem1234 — Wed, 11 Jun 2014 16:35:38 GMT

Maybe you can combine the two dimensions like:

pick(match(PER& '|' & IND, '0|0', '0|0 or 1', '0 or 1|0', '0 or 1|0 or 1'), sum(Value*coef), sum(Value*IND*coef), sum(Value*coef*PER), sum(Value*IND*coef*PER))

Hope this helps!

EDIT: IF you meant just 0 or 1 then:

pick(match(PER& '|' & IND, '0|0', '0|1', '1|0', '1|1'), sum(Value*coef), sum(Value*IND*coef), sum(Value*coef*PER), sum(Value*IND*coef*PER))

Re: Expression optimization

martynlloyd — Wed, 11 Jun 2014 16:53:22 GMT

Try

sum(Value*coef) * IF(IND=0, 1, IND) * IF(PER=0, 1, PER)

Re: Expression optimization

rwunderlich — Wed, 11 Jun 2014 18:16:01 GMT

" because for example PER can aither be 0 or (0 or 1).same for IND."

If the values are only 0 or 1, what's the point in multiplying them? Or are there other values possible?

-Rob

Re: Expression optimization

Thu, 12 Jun 2014 08:02:47 GMT

the fiel PER depending on the user's selection can aither be equal to 0 , or could be both 0 and 1 , in other words

if PER is not equal to 0 , than sum(PER)>=1 . same concept for the FIELD IND

Re: Expression optimization

SergeyMak — Thu, 12 Jun 2014 08:10:54 GMT

Try this

Sum({$<PER={0},IND={0}>}Value*coef)

Sum({$<PER=-{0},IND={0}>}Value*coef*PER)

Sum({$<PER={0},IND=-{0}>}Value*coef*IND)

Sum({$<PER=-{0},IND=-{0}>}Value*coef*IND*PER)

Re: Expression optimization

simondachstr — Thu, 12 Jun 2014 08:15:31 GMT

This will be static and won't reflect possible selections.

Re: Expression optimization

SergeyMak — Thu, 12 Jun 2014 08:17:22 GMT

did you try it?

Re: Expression optimization

simondachstr — Thu, 12 Jun 2014 08:19:20 GMT

Have a look at maybe using SubStringCount() function to imitate select case:

http://community.qlik.com/docs/DOC-5841

Re: Expression optimization

simondachstr — Thu, 12 Jun 2014 08:26:51 GMT

I must say I didn't see the "-" before the "{" - it looked like an extension -{

Edit:

But even so, in your expression e.g. Sum({$<PER={0},IND={0}>}Value*coef) will always calculate even if selected PER & IND are not 0.

In meriem's expression, if PER & IND are unequal 0, sum(Value*IND*coef*PER) only should be calculated.

Re: Expression optimization

Thu, 12 Jun 2014 08:42:51 GMT

Firt of all Thank you for your answer, It doesn't work because as I said before it depends on the user's selection, so this way won't get the needed result

Re: Expression optimization

Thu, 12 Jun 2014 08:58:30 GMT

Thank you the substringcount() is definitly interesting , but In terms of performance for multi million rows it says it didn't change that much from nested IF.

Re: Expression optimization

Thu, 12 Jun 2014 09:33:11 GMT

I was wondering if we can do something like this ,pick(match (IND,0,sum(IND)>=1)

Re: Expression optimization

martynlloyd — Thu, 12 Jun 2014 14:27:02 GMT

I could explain how to use selected values, but as Rob says, if the values of PER and IND are only 0 or 1 then the outcome of your expressions will ALWAYS equal Value*coef
Maybe you could explain with some sample data (numbers)?

Re: Re: Expression optimization

rwunderlich — Thu, 12 Jun 2014 17:30:29 GMT

It seems to me that

sum(Value*coef)

will give you the same answer as the if() statement.

-Rob

Re: Re: Expression optimization

Fri, 13 Jun 2014 10:00:13 GMT

I will try to create a sample application and put it on this forum , since the data model is quite complex, that will help you understand the motivation behind my question.

Thank you for taking time to answer my question