topic Re: Unique count of fields in single column depending upon the condition in QlikView

Unique count of fields in single column depending upon the condition

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to count number of unique countries in single path of travel whose rank is B and funtrip is 1

For eg

Travel Path	Count of Country	=[Fun Trip]
Dubai>Dubai>Dubai>England>England>England>England>England>England>England>England>England>Scotland>Scotland>Scotland>Scotland>Scotland>Scotland>Scotland>Scotland>Dubai>Denmark	22	1

So in above row

Dubai,England,Scotland are countries which are unique countries in travel path which has funtrip 1.

So the result table should look like this

Countries Count

Dubai 1

Denmark 0

England 1

Scotland 1

Hope i am clear with my problem, attached is the QVW and data file

swuehl — Fri, 12 Aug 2016 19:14:07 GMT

Maybe like this?

Country	Sum({<[Fun Trip] = {1}>}Aggr( Count(DISTINCT Country),Path,Country))
Denmark	4
Dubai	4
England	4
Scotland	5

Fri, 12 Aug 2016 19:24:28 GMT

Only one issue, Rank should be B only for the countries to be counted.

Can we have two conditions in that ?

swuehl — Fri, 12 Aug 2016 21:28:23 GMT

Are you looking for

Country	Sum({<[Fun Trip] = {1},Rank={'B'}>}Aggr( Count(DISTINCT Country),Path,Country))
Dubai	4
England	1
Scotland	4

Mon, 15 Aug 2016 18:25:26 GMT

Formula seems to be right but somehow i am not getting the desired count of unique countries.

swuehl — Mon, 15 Aug 2016 18:40:57 GMT

What would be the correct count?

Tue, 16 Aug 2016 17:50:52 GMT

Thanks Stefan,

I was doing something wrong.Your solution worked fine