topic Compare 2 strings in set analysis in QlikView

Compare 2 strings in set analysis

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

Hi community,

I have the following table:

FieldA | FieldB | FieldC

boss boss 4

boss worker 3

boss boss 3

boss worker 4

worker boss 1

worker worker 4

So i want a field where i do the avg of field C when Field A= Field B

I tryed this Sum({$<FieldA= p(FieldB)>}FieldC)/count({$<FieldA=p(FieldB)>}FieldC)

and this

I tryed this Sum({$<FieldA= FieldB>}FieldC)/count({$<FieldA=FieldB>}FieldC)

Neither worked.

Any ideas?
Thanks in advance

Best Regards

Bruno Paulo

vishsaggi — Wed, 06 Dec 2017 22:10:56 GMT

Try this may be?

= Sum(IF(FieldA = FieldB, FieldC))/Count(IF(FieldA = FieldB, FieldC))

Anonymous — Wed, 06 Dec 2017 22:40:41 GMT

Maybe

avg(if(FieldA=FieldB,FieldC))

sunny_talwar — Thu, 07 Dec 2017 01:25:06 GMT

May be create a flag in the script

LOAD FieldA,

FieldB,

FieldC,

If(FieldA = FieldB, 1, 0) as MatchFlag

FROM....

and then this

Sum({<MatchFlag = {1}>}FieldC)/Count({<MatchFlag = {1}>}FieldC)

brunopaulo — Thu, 07 Dec 2017 09:57:31 GMT

Perfect!