Calculate how often customers trade with each other

[sifatnabil]

Hi all,

I have a trades table like this:

TradeID	Customer
1	A
1	B
2	A
2	C
3	A
3	C
3	C

The table shows which customers participated in each TradeID. I want to calculate how often each customer trades with each other. In this example, A has traded with B once, and with C twice. How can I build a table summarizing this for each customer? e.g.

First customer	Second customer	Count
A	B	1
A	C	2
B	A	1
B	C	0
C	A	2
C	B	0

Is this possible? Does this need to be done in script?

[sunny_talwar · Accepted solution]

This?

Script:

Table:

LOAD Distinct *;

LOAD * INLINE [

    TradeID, Customer

    1, A

    1, B

    2, A

    2, C

    3, A

    3, C

    3, C

];

TableCopy:

LOAD DISTINCT TradeID,

  Customer as TradeCustomer

Resident Table;