Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

safai1234
New Contributor

Count non existing observation in script

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Count non existing observation in script

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;

2 Replies
MVP
MVP

Re: Count non existing observation in script

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;

safai1234
New Contributor

Re: Count non existing observation in script

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

Community Browser