topic Re: CountIfs in QlikView

CountIfs

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

Hi,

I am new to Qlik and so help would be much appreciated. I am trying to perform what in Excel would be CountIFS. Counting based on multiple Columns.

I want to be able to count nulls for Multiple Columns: e.g.

Group	Forename	Surname	Age
1	John	Smith	51
1	Sam	Shaw	21
2	Harold
2	Amy
3		Jones

You can see in Forename there is 1 Null, Surname has 2 and Age has 3. I want to be able to count these nulls in various ways.

1. I want to be able to sum the null of multiple columns i.e. Forename + Surname = 3 Nulls

2. I want to be able to sum the null of multiple columns and then base it on multiple groups i.e. Forename + Surname of Group2 = 2 Nulls & Forename + Surname of Group2 +3 = 3 Nulls etc.

I've come across countif through: =count({<Field={'A','B'}>} Field), which I think is a good starting point.

Thank you.

Qlikview Novice

Re: CountIfs

sunny_talwar — Fri, 23 Sep 2016 22:40:38 GMT

May be these if they are not white spaces, but true nulls

1) RangeSum(NullCount(Forename), NullCount(Surname))

2) Not sure I understand your second requirement, can you elaborate?

Re: CountIfs

Digvijay_Singh — Sat, 24 Sep 2016 11:06:40 GMT

I think the 2nd requirement can be met as below -

The top list boxes can be filtered to understand group wise null for selected fields.

Re: CountIfs

alwayslearning — Mon, 26 Sep 2016 13:51:14 GMT

Thanks Digvijay,

The solution provides almost what I need. The one additional thing I hope you can help with is I don't want a listbox to filter by field or group.

i.e. I just want to sum the nulls of multiple fields and groups and return a value in a text box of which ever multiple groupings I need. The other thing is I want to combine the sum of multiple groups. E.g. I want to sum nulls of group 2 & 3 of forename a surname

Thanks

Re: CountIfs

alwayslearning — Mon, 26 Sep 2016 13:51:55 GMT

Hi Sunny,

The first response works great.

I've a bit more detail on number 2 below

Re: CountIfs

sunny_talwar — Mon, 26 Sep 2016 17:08:01 GMT

I still don't understand what you are looking to do. May be digvijay‌ might be able to help better . But in case he is unable to, please provide more details on what you are looking to get.

Best,

Sunny

Re: CountIfs

Digvijay_Singh — Mon, 26 Sep 2016 18:11:05 GMT

Not sure but see if this can help.

Text box 1 - Group wise sum of nulls of all fields, since you don't want list box bases selection, manually you need to remove fields which you want to exclude.

=Sum(Aggr(RangeSum(NullCount(Forename),NullCount(Surname),NullCount(Age)),Group))

Text Box 2 - If you want specific groups and don't want to select groups from list box, then manually you can change groups in set analysis below.

=Sum({<Group={3}>}Aggr(RangeSum(NullCount({<Group={3}>}Forename),NullCount({<Group={3}>}Surname),NullCount({<Group={3}>}Age)),Group))

Re: CountIfs

MarcoWedel — Mon, 26 Sep 2016 21:34:15 GMT

Hi,

maybe this helps:

table1:
LOAD *,
     -IsNull(Forename) as IsNullForename,
     -IsNull(Surname)  as IsNullSurname,
     -IsNull(Age)      as IsNullAge;
LOAD RecNo() as ID,
     Group, 
     If(Len(Forename),Forename) as Forename, 
     If(Len(Surname),Surname)   as Surname, 
     If(Len(Age),Age)           as Age
FROM [https://community.qlik.com/thread/233983] (html, codepage is 1252, embedded labels, table is @1);

table2:
CrossTable (NullField, IsNull)
LOAD ID,
     IsNullForename,
     IsNullSurname,
     IsNullAge
Resident table1;

regards

Marco

Re: CountIfs

alwayslearning — Tue, 27 Sep 2016 01:15:24 GMT

Thanks Sunny for the Help

& Thanks Digvijay

Your second part:

=Sum({<Group={3}>}Aggr(RangeSum(NullCount({<Group={3}>}Forename),NullCount({<Group={3}>}Surname),NullCount({<Group={3}>}Age)),Group))

This worked for me, however my actual code is a for projects and looks something like:

=Sum({<[Project ID]={PJ0001}>}Aggr(RangeSum(NullCount({<[Project ID]={PJ0001}>}Name),NullCount({<[Project Id]={PJ0001}>}[Description])),[Project ID])) - Even though you see I am only checking two fields for Nulls the value coming up showed up as 3?

However I believe Marco has given a different solution which worked for me. By manipulating the data, making nulls = 1 and nonenull= 0

Re: CountIfs

alwayslearning — Tue, 27 Sep 2016 01:17:56 GMT

Hi Marco, Thanks for this. This seems to work perfectly for me.

I slightly edited your code for IsNull in the load script, and one line placed Nulls = 1 and none null = 0

if(IsNull(ForeName)=0, ForeName=0, '1') As Nullname

Re: CountIfs

MarcoWedel — Tue, 27 Sep 2016 19:39:52 GMT

you're welcome.

Glad it worked.

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

Re: CountIfs

Digvijay_Singh — Wed, 28 Sep 2016 05:28:32 GMT

Good to hear that marcowedel 's solution worked for you

In your expression you have used [Project Id] at once place, in different case than [Project ID], that may cause problem.

You can close this thread by marking one correct answer and as many helpful, likes as you feel.

take care,