1 Reply Latest reply: Jul 12, 2011 5:28 PM by John Witherspoon RSS

    Comparing 2 fields

      I have what may be a simple question - each row of data has 3 fields with the corresponding values

       

      Field A has values 1,1,2,2,3,3,4,4,5,5  (10 values)

      Field B has values 1,2,4,2,4,4,4,5,5,4

      Field C has values 20,30, 20, 30 ,40 ,45,46, 47,50,60

       

      how do I select rows where field A = field B to sum Field C - i.e. row 1, row 4 ,row 7, and row 9

      and sum Field C i.e 20 + 30 + 46 + 50 = 146

       

      I also need to know how to select rows where Field A value is less than Field B values i.e rows 2,3,5,6,8

      and where Field A is greater than Field B i.e. row 10

       

      I have tried set analysis  -    sum( {<Field A = Field B>} Field C)    but didn't work

      also tried (for less than)       sum( {<Field A = {$=( < Field B)} >} FieldC)

       

      and many other combinations and syntax

       

      Thanks

        • Re: Comparing 2 fields
          John Witherspoon

          sum(if(FieldA=FieldB,FieldC))
          sum(if(FieldA<FieldB,FieldC))
          sum(if(FieldA>FieldB,FieldC))

           

          I believe set analysis can't work because it isn't sensitive to row.  So if you tell it FieldA=FieldB, I would expect this to work the same as FieldA={1,2,4,5}, which isn't what you want.  Ah, I was wrong.  It only works if a single value is selected for FieldB.  If you need the performance of set analysis, consider setting and using an indicator.

           

          ,if(FieldA=FieldB,'='
          ,if(FieldA<FieldB,'<'
          ,if(FieldA>FieldB,'>','?'))) as "A vs. B"

           

          sum({<"A vs. B"={'='}>} FieldC)
          sum({<"A vs. B"={'<'}>} FieldC)
          sum({<"A vs. B"={'>'}>} FieldC)

           

          You could also do this if there's a unique ID associated with each row.

           

          sum({<ID={"=FieldA=FieldB"}>} FieldC)

           

          However, I expect the performance of that to be just as bad as the sum(if()), as both need to scan EVERY row in order to know what to sum.

          See all three approaches in the attached example.