topic Re: Count of fields in QlikView

Count of fields

Anonymous — Wed, 15 Oct 2014 11:21:06 GMT

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?

Re: Count of fields

Wed, 15 Oct 2014 11:52:07 GMT

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

Re: Count of fields

tresB — Wed, 15 Oct 2014 11:57:13 GMT

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

Re: Count of fields

anbu1984 — Wed, 15 Oct 2014 12:21:15 GMT

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;

Re: Count of fields

Anonymous — Wed, 15 Oct 2014 14:11:12 GMT

That's the ticket. Thanks very much