1 Reply Latest reply: Jul 30, 2018 6:24 PM by Daniel Berkes RSS

    Count by Group

    Mikhail Morales

      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.

        • Re: Count by Group
          Daniel Berkes

          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)