topic How do you perform a Count IF statement over various fields in QlikView

How do you perform a Count IF statement over various fields

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

Hi, hoping someone can help me with working Count If statements

I have been trying to produce a count if expression in a table based on how i know it does work in excel and can not get the answer i need.

Sample data with correct answer in excel :-

client1	client2	accounta	accountb	accountc	=Countif(clienta:clientc,">0")
abc	123	0	1	3	2
abc	234	0	0	0	0
123	cde	5	9	7	3
cde	568	0	2	0	1

I am just trying to count each field that has a value in. Regardless of what the value is in the cell it would give that cell a count of 1 then in the new field the count of 1's would be added up so in the above example i have 3 fields i am counting so my new field would never have a count greater than 3

In Qlik i have tried variations of the following

count(if(accounta='>0' or accountb='>0' or accountc='>0',1))

Ideally id like to put this in the load script so that i can produce a new field with the count in but even if i can just put it in an expresssion at the end of my table that would work

thanks

Re: How do you perform a Count IF statement over various fields

sunny_talwar — Fri, 31 Mar 2017 15:09:28 GMT

May be try this

Sum(RangeMin(accounta, 1), RangeMin(accountb, 1), RangeMin(accountc, 1))

Re: How do you perform a Count IF statement over various fields

Anonymous — Fri, 31 Mar 2017 15:19:22 GMT

thanks for relying but that does not appear to work in my table expression. Or would this work in the script load?

Re: How do you perform a Count IF statement over various fields

sunny_talwar — Fri, 31 Mar 2017 15:27:29 GMT

My bad, try this

Sum(RangeMin(accounta, 1) + RangeMin(accountb, 1) + RangeMin(accountc, 1))

Re: How do you perform a Count IF statement over various fields

Anonymous — Fri, 31 Mar 2017 15:53:14 GMT

thank you soooooo much ive spent days trying to work this out! do you know if i can add it in to the script as a new field?

Re: How do you perform a Count IF statement over various fields

sunny_talwar — Fri, 31 Mar 2017 16:04:34 GMT

Sure, this should work in the script also...

RangeMin(accounta, 1) + RangeMin(accountb, 1) + RangeMin(accountc, 1) as NewField

Re: How do you perform a Count IF statement over various fields

Anonymous — Fri, 31 Mar 2017 16:24:52 GMT

thank you so much your help really appreciate it