topic Re: Count names from 2 fields in same table in QlikView

Count names from 2 fields in same table

sifatnabil — Wed, 12 Mar 2014 06:23:21 GMT

Hi,

I have 2 fields in a table called "Buyer Broker" and "Seller Broker", and the same "broker" may populate in both fields, e.g.

Buyer Broker | Seller Broker

------------------|------------------

Andrew Nathan

Andrew Andrew

(null) (null)

Nathan Andrew

I would like to count the frequency of each broker's name from both fields and sum them, and then group by broker, e.g.

Andrew - 4

Nathan - 6

How could this be achieved? Thank you.

Re: Count names from 2 fields in same table

Anonymous — Wed, 12 Mar 2014 06:34:45 GMT

PFA

Re: Count names from 2 fields in same table

er_mohit — Wed, 12 Mar 2014 07:01:55 GMT

see the attached file

Re: Count names from 2 fields in same table

maxgro — Wed, 12 Mar 2014 07:07:29 GMT

RESULT

In dim flag suppresss when value is null

Broker	count([Broker])
Andrew	4
Nathan	2

SCRIPT

Source:

load rowno() as id,

if (match(trim([Buyer Broker]), '(null)'), null(), [Buyer Broker]) as [Buyer Broker],

if (match(trim([Seller Broker]), '(null)'), null(), [Seller Broker]) as [Seller Broker]

;

load * inline [

Buyer Broker , Seller Broker

Andrew , Nathan

Andrew , Andrew

(null), (null)

Nathan , Andrew

];

Tab:

load id, [Buyer Broker] as Broker, 'Buyer' as BrokerType

Resident Source;

Concatenate (Tab)

load id, [Seller Broker] as Broker, 'Seller' as BrokerType

Resident Source;

Re: Count names from 2 fields in same table

sifatnabil — Thu, 13 Mar 2014 03:26:49 GMT

Hi Nitin and Mohit,

Your solutions are similar but I have a question:

Why is there a noConcatenate statement between T1 and T2? As there are no common field names, is it necessary to use noConcatenate there? See attached image.