Comparing 2 fields

Tue, 12 Jul 2011 20:45:12 GMT

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

johnw — Tue, 12 Jul 2011 21:28:31 GMT

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.

topic Re: Comparing 2 fields in QlikView

Comparing 2 fields

Re: Comparing 2 fields