7 Replies Latest reply: Feb 6, 2018 5:54 AM by omar bensalem RSS

    Count items group by name based on status

    Mei Zheng Tay

      Hi,

       

      I have a table of items with status 'Missing' or 'Installed'. Simplified table below:

      NameStatus
      AMissing
      BInstalled
      BInstalled
      CMissing
      CInstalled

       

      How can I achieve this calculation of total installed and missing items?

      NameTotalInstalledMissing
      A101
      B220
      C211

       

      I managed to get the correct numbers but am not able to display it in the way I want to (as above) with the following codes

       

      hostnameTotals:

      Load "Host Name",

      count(distinct %PWID) as "PW.Total"

      Resident tempTable

      group by "Host Name";

       

      hostnameInstalled:

      Load "Host Name",

      if([PW.Status] like 'Installed', count(distinct %PWID)) as "PW.Installed",

          if([PW.Status] like 'Missing', count(distinct %PWID)) as "PW.Missing"

      Resident tempTable

      group by "Host Name", [PW.Status];