Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Each month i receive information (in excel) about salary the following way:
Company | Country | Salary |
Microsoft | USA | 500 |
Microsoft | India | 200 |
Microsoft | Russia | 300 |
IBM | India | 200 |
IBM | China | 300 |
IBM | Russia | 600 |
Apple | USA | 1000 |
Apple | Russia | 300 |
This list only shows the countries with existing salary. What i want to make in my script is a code that will give me the following:
Company | Country | Salary |
Microsoft | USA | 500 |
Microsoft | India | 200 |
Microsoft | China | 0 |
Microsoft | Russia | 300 |
IBM | USA | 0 |
IBM | India | 200 |
IBM | China | 300 |
IBM | Russia | 600 |
Apple | USA | 1000 |
Apple | India | 300 |
Apple | China | 0 |
Apple | Russia | 0 |
In other words i want my script to generate the non existing observation so i can make a pivot table that explicitly shows all countries regardless of whether there was a salary or not (as is the case for Apple in China and Russia).
Can anyone tell me how to do this when the original data has many variables and observations. ?
Kind regards,
safai1234
See attached example
Thank you very much for your quick answer.
I still have problems, but i think you know where i'm going. Perhaps i didn't gave i good enough description of my problem.
Here is an exactly description.
The variable "salary" is actually a variable i count. So think of it as an employee variable that contains some kind of ID. 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 figure in the data.
Here is an illustration:
Data:
Company | Country | Employee (ID number) |
Microsoft | USA | 232 |
Microsoft | USA | 568 |
Microsoft | USA | 115 |
Microsoft | Russia | 8787 |
Microsoft | Russia | 9898 |
Microsoft | Russia | 5656 |
Microsoft | Russia | 6568 |
Microsoft | India | 111111 |
IBM | USA | 989 |
IBM | USA | 854 |
IBM | China | 65 |
IBM | China | 23 |
IBM | China | 54 |
IBM | India | 222222 |
Pivot table definition:
count(
{<
Company = {Microsoft, IBM},
Country = {USA, Russia, China}
>}
Employee (ID number))
Desired output:
Company | Country | Total |
Microsoft | USA | 3 |
Russia | 4 | |
China | 0 | |
IBM | USA | 2 |
Russia | 0 | |
China | 3 |
How do i get China and Russia in the pivot table when they aren't in the data?
Kind regards,
safai1234