Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
drunk8gods
New Contributor

Count by Group

Hi all,

New to Qlik Sense, so I apologize if this is a simple question in advance.

Here is a sample of the data I'm working with:

EmployeeIDStatusStatus Number
0001Not Fixed0

0001

Not Fixed0
0002Not Fixed0

0002

Fixed1
0003Fixed1
0004Not Fixed0
0004Fixed1
0005Not Fixed0

The goal is to be able to do a count of Status = Not Fixed by EmployeeID.  But if there is an entry where an EmployeeID has a status of Fixed, the Not Fixed should be excluded in the count.  Essentially we want a count of EmployeeIDs where ONLY Not Fixed exists.

So, for instance, In the dataset above, the KPI count of Not Fixed would be 2. EmployeeID 0001 has 2 entries of Not Fixed, so it should be counted as 1. EmployeeID 0002 and 0004 have a Fixed and Not Fixed so they're excluded in the KPI Count.  Employee 0005 only has Not Fixed so it is included.

The status number is just an if(Status = 'Fixed',1,0)

I'm drawing a blank as to how to do this distinct count.  Is this something that can be created as a dimension or should a new dimension be created in the load?  Any help is appreciated.

1 Reply
Highlighted
Partner
Partner

Re: Count by Group

Hi since this info is staticI would create a flag at Back End after would use set analysis.

I Did not teste it but you can understand the logic and adjust it.

Back end:

map_FinalStatus:

mapping LOAD

     EmployeeID,

     if(WildMatch(everySatus,'*Not Fixed*') ,0,1) as finalStatus;

LOAD Distinct

     EmployeeID,

     Status,

     concat(Status,'/') as everyStatus

Resident myBigTable

Group By EmployeeID,Status

order by Status asc;

temp_myBigTable:

NoConcatenate

LOAD *,

applymap('map_FinalStatus',EmployeeID) as flag_finalStatus

Resident myBigTable;

drop Table myBigTable;

RENAME Table temp_myBigTable to myBigTable;

front end:

count(distinct {<flag_finalStatus={1}>}EmployeeID)