topic Re: Create non existing observations in script in QlikView

Create non existing observations in script

Anonymous — Tue, 12 Feb 2013 15:05:24 GMT

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

Re: Create non existing observations in script

Gysbert_Wassenaar — Tue, 12 Feb 2013 15:21:07 GMT

See attached example

Re: Create non existing observations in script

Anonymous — Wed, 13 Feb 2013 09:22:11 GMT

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