topic Re: Count non existing observation in script in QlikView

Count non existing observation in script

Anonymous — Wed, 13 Feb 2013 09:57:10 GMT

Hi,

Here is a description and an illustration of the problem:

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

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:

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 — Wed, 13 Feb 2013 10:29:16 GMT

Maybe like this:

INPUT:

LOAD Company,

Country,

[Employee (ID number)]

FROM

(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 — Fri, 15 Feb 2013 09:35:39 GMT

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