Count non existing observation in script

[Anonymous]

Hi,

Here is a description and an illustration of the problem:

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

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

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	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 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

[swuehl · Accepted solution]

Maybe like this:

INPUT:

LOAD Company,

     Country,

     [Employee (ID number)]

FROM

[http://community.qlik.com/thread/72547?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

TMP:

LOAD DISTINCT Company resident INPUT;

JOIN (TMP) LOAD DISTINCT Country Resident INPUT;

RIGHT JOIN (INPUT) LOAD * Resident TMP;

drop table TMP;

[Anonymous]

Hi swuehl,

Thank you very much for your answer. It was exactly what i needed i just didn't know that you could join the variables in that way.

You are my boss' hero (:

Kind regards