if statement location in expression

[Anonymous]

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))

OR

=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!

[Anil_Babu_Samineni]

I will try my best

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

OR

=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

[sasiparupudi1]

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?

[Anonymous]

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!

[sunny_talwar]

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)