topic Re: if statement location in expression in QlikView

if statement location in expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone! Thank you so much for taking the time to read my question and potentially answer my question! I have a very basic overview, type of question:

What is the resulting difference between the following two expression?

=sum(if(x>5, countOfFruit))

=if(x>5, sum(countOfFruit))

So if I have a list of fruit and the various numbers of each specific fruit, which would be the correct was of summing all the fruits that had a greater value than 5?

Thank you!

Re: if statement location in expression

Anil_Babu_Samineni — Tue, 21 Feb 2017 14:03:44 GMT

I will try my best

=sum(if(x>5, countOfFruit)) //This will return as Sum(countOfFruit) Works and it show the Sum of Total

=if(x>5, sum(countOfFruit)) // This will return as Sum(countOfFruit) Works and it won't show the Sum of Total

Instead of that, I would highly call this

Sum({<x = {'>5'}>}countOfFruit)

For better understand

Re: if statement location in expression

sasiparupudi1 — Tue, 21 Feb 2017 14:17:02 GMT

Best option would be to use set analysis

sum({<x={'>5'}>}CountOfFruit)

Second best would be to use sum(if(x>5,CountOfFruit))

Please go through the link

When is it best to use sum(if()) over Set Analysis in an expression?

Re: if statement location in expression

Anonymous — Tue, 21 Feb 2017 14:52:04 GMT

Thank you!

So the first option ( 'sum(if(x>5,....))' is the old version of set analysis?

What if I had three columns, one that fruit at time A (fruitA) and then fruit at time B (FruitB) -> how would I be able to say that I would only like the expression to sum the values when FruitA does not equal FruitB?

So :

FruitA FruitB CountOfFruit (want in sum)

Apple Apple 6 (no)

Pear Apple 10 (yes)

Pear Pear 7 (no)

Apple Pear 8 (yes)

So I would want it to return value as 18. Would it be (if I were to use set analysis):

= sum( {<FruitA<>FruitB>} CountOfFruit)

Thanks!

Re: if statement location in expression

sunny_talwar — Tue, 21 Feb 2017 15:12:58 GMT

You would need to create a new field in the script which uniquely identify each row

LOAD RowNo() as Key,

FruitA,

FruitB,

CountOfFruit

FROM ....;

and then this

=Sum({<Key = {"=FruitA <> FruitB"}>} CountOfFruit)

or you can create a flag in the script

LOAD FruitA,

FruitB,

If(FruitA = FruitB, 1, 0) as Flag,

CountOfFruit

FROM ....;

and then this

=Sum({<Flag= {0}>} CountOfFruit)