topic Re: Count amount of entries for a dimension in QlikView

Count amount of entries for a dimension

Wed, 06 Jul 2016 18:44:22 GMT

I have a dimension which is a list of unique countries ("Country(unique)") with a measure of rows ("Nid" which acts as a primary key and ID for each row). Each row in my data contains a field that lists one or multiple countries ("Countries") separated by commas. I want to count the number of rows (Nid) for which each unique country appears.

Using the following formula, I am able to count the number of rows that contains (and only contains) Brazil.

Count({<Countries={'Brazil'}>}Nid)

Instead, I need to count all rows that contains the country and any other text/country for each country listed as a dimension. How do I go about doing this?

Re: Count amount of entries for a dimension

sunny_talwar — Wed, 06 Jul 2016 19:25:46 GMT

Not 100% sure, but may be this:

Count({<Countries={'*Brazil*'}>}Nid)

Re: Count amount of entries for a dimension

Wed, 06 Jul 2016 19:36:14 GMT

The wildcard works to count anything including the word Brazil, so we're partly there. The bigger challenge is doing the same thing for each dimension (country).

Re: Count amount of entries for a dimension

sunny_talwar — Wed, 06 Jul 2016 20:07:38 GMT

I think it would be better to make changes in the backend of the script to handle this in a better way.

Create a new table like this:

LOAD Countries,

SubField(Countries, ',') as Country(unique)

Resident SomeTable;

Where Country(unique) name matches with the field name for the field where Country is uniquely listed and is tied to Nid