topic How to select or exclude values based on other fields in set expression? in QlikView

How to select or exclude values based on other fields in set expression?

Fri, 28 Sep 2012 07:06:10 GMT

Hi,

I want my expression to have only set expression for speed and memory (use a lot of data...)

But I can't figger out how to do the following in a set expression, I never get the right answer when my pivot is expanded and collapsed... Any help?

AVG({$<Field1={1,2,3}, Field2={4}>} if(Field5=Field6,'',Field5))

I thought something like this:

AVG({$<Field1={1,2,3}, Field2={4}, Field5-=Field6>} Field5)

But I tried a lot already...

Thanks in advance!

Regards,

Bram

Re: How to select or exclude values based on other fields in set expression?

Anonymous — Fri, 28 Sep 2012 07:14:11 GMT

Hi Bram,

Try something like this:

avg({$<Field1={1,2,3}, Field2={4},Field5={$(=only(Field6))}>} Field5)

Jonathan

Re: How to select or exclude values based on other fields in set expression?

Fri, 28 Sep 2012 09:07:04 GMT

Thanks for your answers, but now it still takes values when Field5=Field6... The If doesn't do this...

See screenshot, actual expression in screenshot is:

AVG({$<Field2={AA}, Field3={1}>} if(Field5=Field6,'',Field1))

As you can see only the if statement gets the right results...

Second expression is: AVG({$<Field2={AA}, Field3={1}, Field5-={$(=only(Field6))}>} Field1)

Third: AVG({$<Field2={AA}, Field3={1}, Field5-={"Field6"}>} Field1)

Re: How to select or exclude values based on other fields in set expression?

Mon, 01 Oct 2012 05:59:32 GMT

No one?

Re: How to select or exclude values based on other fields in set expression?

jonathandienst — Mon, 01 Oct 2012 06:19:24 GMT

Bram

The set expression is evaluated once, ouside of the chart, and has no knowledge of the chart dimensions. In this case, this means that it does not know which value of Field6 it must compare to Field5. You original Sum(If()) expression is evaluated inside the chart and is probably the correct solution for your problem.

If you want to ensure that Field5 is equal to ANY possible value (based on selections) of Field6, then you could try

=AVG({$<Field1={1,2,3}, Field2={4}, Field5=P(Field6)>} Field5)

But I suspect that this is not correct for your requirements. If Sum(If()) gives the correct result and the set expression above does not, then you may want to add a flag field in your load script, like this:

LOAD ...

Field5,

Field6,

If(Field5 = Field6, 1, 0) As Field56Flag,

...

And then use the flag in your set expression, like this:

=AVG({$<Field1={1,2,3}, Field2={4}, Field56Flag={1}>} Field5)

Hope that helps

Jonathan

Re: How to select or exclude values based on other fields in set expression?

Mon, 01 Oct 2012 06:28:50 GMT

Hi Jonathan,

Thanks for the help, then I guess I'll stick to the if() for now, field5 and field6 are not in the same table, so I would have to join them first to do a comparison in the load script...

If I run in to too many 'object ran out of memory' I'll change my load script 🙂

Thanks!

Re: How to select or exclude values based on other fields in set expression?

jonathandienst — Tue, 02 Oct 2012 11:21:04 GMT

Bram

The fact that they are in different tables expains why QV takes memory and time to calculate the expression. It has to do an in-momory join to complete the calculation and this can be resource intensive.

Regards

Jonathan