2 Replies Latest reply: Feb 15, 2013 4:35 AM by Daniel Safai RSS

    Count non existing observation in script

    Daniel Safai

      Hi,

       

      Here is a description and an illustration of the problem:

       

      Each month i receive information (in excel) about salary the following way:

       

      Company

      CountryEmployee (ID number)
      MicrosoftUSA232
      MicrosoftUSA568
      MicrosoftUSA115
      MicrosoftRussia8787
      MicrosoftRussia9898
      MicrosoftRussia5656
      MicrosoftRussia6568
      MicrosoftIndia111111
      IBMUSA989
      IBMUSA854
      IBMChina65
      IBMChina23
      IBMChina54
      IBMIndia222222

       

       

      I want a pivot table that counts the number of employees in some chosen countries and reports 0 for countries with no employees.

      The problem is that countries without employees doesn't get attached to the company.

       

      Pivot table definition:

       

      count(

      {<

      Company = {Microsoft, IBM},

      Country = {USA, Russia, China}

      >}

      Employee (ID number))

       

       

      Here is an illustration of the desired output:

       

      Company

      CountryTotal
      MicrosoftUSA3
      Russia4
      China0
      IBMUSA2
      Russia0
      China3

       

      How do i get China and Russia in the pivot table when the countries doesn't appear with the country?

      (Don't know if it is relevant but the data contains a lot of variables and a lot of observations)

       

      Kind regards,

       

      safai1234