Count names from 2 fields in same table

[sifatnabil]

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.

[Anonymous · Accepted solution]

PFA

[er_mohit]

see the attached file

[maxgro]

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;

[sifatnabil]

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.