Solved: HELP: Count number of companies once in table - Qlik Community

Anonymous · ‎2013-09-28

Hello all,

I am having a silly problem trying to count different companies in a table.

Imagine that I have a table with the following values

CompanyType	Company
Bank	ING Direct
Bank	BBVA
Telco	Telefonica
Insurance	BBVA
Insurance	Sanitas

Note that BBVA appears twice. I need to build a table that counts the distinct companies only once in the whole table. See the desired table:

CompanyType	Count
Bank	2
Telco	1
Insurance	1*

* I do not want to count BBVA again as it was allready included in the first row.

I had tried many different things using a combination of above and set analysis but I could not make it work.

I will be happy if you can help me to find a solution.

Regards and thanks in advance

Report Inappropriate Content · ‎2013-09-30

Javier,

I'm posting the file again, see if you can open it now. And in this way solves your problem. I used a grouping by company and efetuei counting on this group.

View solution in original post

Anonymous · ‎2013-09-28

Javier

Try something like:

Create a Chart of type Straight Table
Use CompanyType as a dimension
And as an expression Count ( distinct Company )

Best Regards, Bill

Anonymous · ‎2013-09-28

Bill,

Thanks for your reply. I understand what you mean, but this is the standar behaviour I want to avoid. If I do this the table I get is the following one:

CompanyType	Count
Bank	2
Telco	1
Insurance	2

But as BBVA has already counted it the first row as a Bank I do not want it being counted again. So the table I need is:

CompanyType	Count
Bank	2
Telco	1
Insurance	1

Hope it is more clear now.

Regards

Report Inappropriate Content · ‎2013-09-28

Javier;

As Bill said, if you create a PivotTable or a simple table with the type of company as dimension, the values that you want will occur. see the attachment.

Anonymous · ‎2013-09-28

Thanks,

Put in your example BBVA is counted twice, one in Bank and also in Insurance. What I need is to count BBVA only once, in this example in Bank. So the insurance row must show 1 as a count, not 2.

Regards,

Javier

Report Inappropriate Content · ‎2013-09-28

Javier,

See if this solves your problem so

Anonymous · ‎2013-09-28

Sorry Claudio, I cannot open this document. Can you upload it again?

Thanks

Anonymous · ‎2013-09-28

Thanks Bill,

It is interesting but in the table, in the row insurance, it should show the number 1, and it is showing the number 2.

Thanks a lot for your help

Anonymous · ‎2013-09-28

Javier

How about this attached qvw ?

Best Regards, Bill

Anonymous · ‎2013-09-28

Beautiful... I now need to integrate with my QV.

Thanks!!