Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
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.
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
Hi Sunny,
The first response works great.
I've a bit more detail on number 2 below
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
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))
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
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
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