Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jchacko_rxsense
Contributor II
Contributor II

Count records from table based on conditions

 

I have one table:

TAB A
TaskName, UserID, UserEnabled 
A,1,T
A,2,T
A,3,F
B,4,F
B,5,F
C,6,T
C,7,T
D,8,F
D,9,NULL

I need to count Distinct TaskNames for only the tasks having UserEnabled as 'F' or NULL only.

So the output should be:

TaskName
B
D

Distinct count should be 2.

Labels (7)
1 Solution

Accepted Solutions
rwunderlich

Here it is dealing with real null(). 

=Count({<TaskName ={"=UserEnabled='F' or IsNull(UserEnabled)"}>
- <TaskName = P({<UserEnabled ={'T'}> } TaskName)> } DISTINCT TaskName)

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

View solution in original post

7 Replies
rwunderlich

=Count({<UserEnabled ={'F','NULL'}> - <TaskName = P({<UserEnabled -={'F','NULL'}>} TaskName)> } DISTINCT TaskName)

-Rob

jchacko_rxsense
Contributor II
Contributor II
Author

Hey thanks for the revert but the output of this I am getting zero. Also if possible could you please explain me the above script as well.

 

PrashantSangle

@jchacko_rxsense , I tried with @rwunderlich answer, it is giving me correct answer. How you are trying it? & where you are trying it?

Great dreamer's dreams never fulfilled, they are always transcended.
jchacko_rxsense
Contributor II
Contributor II
Author

One question, Is the 'NULL' in the code above being read as a text value or as NULL value which is usually shown as - in table.

Also I am trying in on my KPI in front end.

 

rwunderlich

In the expression, I was treating NULL as a text value. If you meant actual null, I would need to modify the expression.

-Rob

jchacko_rxsense
Contributor II
Contributor II
Author

Yes please, I want to use actual NULL.

To explain the requirement again. I need to find the distinct count of TaskName which have their entire UserEnabled as the following combinations:

TaskName, User, UserEnabled
A, 1, False
A, 2, False
B, 3, False
B, 4, Null (Not text value. This is actual NULL)
C, 5, Null
So the distinct count of the TaskName will be 2 (TaskName: A,B,C will be counted).

So I want TaskNames which have all their users as False or NULL(Not text value but actual NULL).

rwunderlich

Here it is dealing with real null(). 

=Count({<TaskName ={"=UserEnabled='F' or IsNull(UserEnabled)"}>
- <TaskName = P({<UserEnabled ={'T'}> } TaskName)> } DISTINCT TaskName)

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com