topic Re: Counting different columns in the same row? in QlikView

Counting different columns in the same row?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Counting different columns in the same row?

sunny_talwar — Fri, 08 Apr 2016 16:05:55 GMT

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:

Re: Counting different columns in the same row?

swuehl — Fri, 08 Apr 2016 16:06:31 GMT

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)

Re: Counting different columns in the same row?

swuehl — Fri, 08 Apr 2016 16:07:46 GMT

Re: Counting different columns in the same row?

Anonymous — Sun, 10 Apr 2016 21:22:19 GMT

thank you Sunny! you are a star!