Count of fields

[Anonymous]

Hi guys,

I have the following table:

Customer number,

Account number

Balance

I'm looking to put together a table which shows the number of accounts people hold. For example

Accounts held -- number of customers

1 -- 5

2 -- 1

3 -- 0

4 -- 8

Can anyone think of a way i can do this?

[#2]

Try This:

Script:

LOAD  * INLINE [
    CustomerID, BankAccount
    5, B1
    5, B2
    6, B3
    6, B4
    6, B5
    7, B6
    7, B7
    8, B8
    9, B9
    9, B10 
];

Create a Chart with one calculated Dimension:

aggr(count(BankAccount), CustomerID)

And Expression:

count(Distinct  CustomerID)

I think thats what you need

Regards,

Angel

[tresesco · Accepted solution]

In the script, something like:

Input:

Load

     [Customer number],

     [Account number],

     Balance

From <>;

Left Join

Load

          [Account Number],

          Count([Customer Number]) as NoOfCustomer

Resident Input Group By [Account Number];                       // Edit - Group by

[anbu1984]

Initial:

LOAD  * INLINE [

    CustomerID, BankAccount

    5, B1

    5, B2

    6, B3

    6, B4

    6, B5

    7, B6

    7, B7

    8, B8

    9, B9

    9, B10

];

Final:

Load AccountsHeld,Count(AccountsHeld) as CustCnt Group by AccountsHeld;

Load Count(BankAccount) As AccountsHeld Resident Initial Group by CustomerID;

[Anonymous]

That's the ticket. Thanks very much