Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

CountIfs

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.

 

GroupForenameSurnameAge
1JohnSmith51
1SamShaw21
2Harold
2Amy
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

1 Solution

Accepted Solutions
Highlighted

Hi,

maybe this helps:

QlikCommunity_Thread_233983_Pic1.JPG

QlikCommunity_Thread_233983_Pic2.JPG

QlikCommunity_Thread_233983_Pic3.JPG

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

View solution in original post

11 Replies
Highlighted

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?

Highlighted
Master III
Master III

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.

Capture.JPG

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

Hi Sunny,

The first response works great.

I've a bit more detail on number 2 below

Highlighted

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

Highlighted
Master III
Master III

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))

Highlighted

Hi,

maybe this helps:

QlikCommunity_Thread_233983_Pic1.JPG

QlikCommunity_Thread_233983_Pic2.JPG

QlikCommunity_Thread_233983_Pic3.JPG

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

View solution in original post

Highlighted
Creator
Creator

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


Highlighted
Creator
Creator

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