Counting different columns in the same row?

[Anonymous]

Hi Qlik Gurus,

I have a question and am not sure how to solve.

I have two columns of countries (creator country and Receiver country).

for each country, count the creator country and receiver country in the same line.

For example, AU appears 3 times as a Creator and 1 time as a Receiver.

Any ideas how to go about resolving this?

thanks

this is the raw data:

ID	Creator	Receiver	Ref
I0001	AU	TH	AU-TH
I0011	AU	UK	AU-UK
I0017	AU	MY	AU-MY
I0002	MY	AU	MY-AU
I0004	MY	UK	MY-UK
I0008	MY	UK	MY-UK
I0021	MY	US	MY-US
I0003	SG	UK	SG-UK
I0009	TH	US	TH-US
I0010	TH	MY	TH-MY
I0015	TH	US	TH-US
I0016	TH	UK	TH-UK
I0022	TH	UK	TH-UK
I0005	UK	US	UK-US
I0007	UK	TH	UK-TH
I0012	UK	US	UK-US
I0013	UK	MY	UK-MY
I0018	UK	TH	UK-TH
I0019	UK	MY	UK-MY
I0023	UK	MY	UK-MY
I0006	US	MY	US-MY
I0014	US	UK	US-UK
I0020	US	UK	US-UK
I0024	US	TH	US-TH

the result i want to display is:

	Creator	Receiver
AU	3	1
MY	4	6
SG	1	0
TH	5	4
UK	7	8
US	4	5

[sunny_talwar · Accepted solution]

Check out the attached

Script:

Leads:

LOAD ID,

     Creator,

     Receiver,

     Ref

FROM

[https://community.qlik.com/thread/212579]

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

LinkTable:

LOAD ID,

  Creator as Country,

  'Creator' as Flag

Resident Leads;

Concatenate (LinkTable)

LOAD ID,

  Receiver as Country,

  'Receiver' as Flag

Resident Leads;

Data Model:

[swuehl]

You could load your table like

CROSSTABLE (Actor, Country)

LOAD ID, Creator, Receiver

FROM ...;

Then create a pivot table chart with dimensions Country and Actor and expression

=Count(DISTINCT ID)

[swuehl]

See also:

The Crosstable Load

[Anonymous]

thank you Sunny! you are a star!