Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Reply
johnw
Champion III
Champion III

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.