Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
m_perreault
Contributor III

And vs Or

I am reviewing someone's script and I cannot tell if an 'And vs Or' situation is creating errors in the data.

The script uses a where clause

'

and (

(Type<>'Fund' and Type<>'Other')

or IsChildPortfolio='False'

) '

Should the script instead by

and (

Type<>'Fund' or Type<>'Other')

or IsChildPortfolio='False'

) '

Is there a difference between the two?

1 Solution

Accepted Solutions
MVP
MVP

Re: And vs Or

There is a huge risk with using "bare" fields in any measure in Qlik Sense. You should always (or almost always) use aggregation functions with all field in measures.

If your data returns more than one value for each "row" in a chart/table then a bare field reference will not know what to do with it. So it will turn into a NULL and the full expression might not calculate at all or return the wrong answer.

In your example screenshot above I can't see if multiple values will be returned for each of the fields in your measures.

Henric Cronström has written several blogs/documents about how you should always use an aggregation function and I advice you to read them thoroughly to understand why:

Even though they are all written in the context of QlikView they are 100% relevant for Qlik Sense....

5 Replies
MVP
MVP

Re: And vs Or

Clearly they are different and the two script snippets will give different results.

Just try to calculate manually when Type is equal to 'Fund' and see what you get for both script snippets - different results. For the first parenthesis the first snippet will return false and the second snippet will return true.

So it might be that the logic should be changed.

m_perreault
Contributor III

Re: And vs Or

Thanks Petter, could you describe a situation in which you would want to use an AND operation on the same Field?

MVP
MVP

Re: And vs Or

It seems to me that this has to be taken into a larger context and can't be done in isolation.

m_perreault
Contributor III

Re: And vs Or

Sure, could you explain how Qlik handles the following.  It seems counter intuitive to me Screen Shot 2018-04-30 at 9.26.52 PM.png

MVP
MVP

Re: And vs Or

There is a huge risk with using "bare" fields in any measure in Qlik Sense. You should always (or almost always) use aggregation functions with all field in measures.

If your data returns more than one value for each "row" in a chart/table then a bare field reference will not know what to do with it. So it will turn into a NULL and the full expression might not calculate at all or return the wrong answer.

In your example screenshot above I can't see if multiple values will be returned for each of the fields in your measures.

Henric Cronström has written several blogs/documents about how you should always use an aggregation function and I advice you to read them thoroughly to understand why:

Even though they are all written in the context of QlikView they are 100% relevant for Qlik Sense....