topic Count distinct row in QlikView

Count distinct row

maxime66 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlikers,

Is there a simple expression to calculate distinct values for many columns ?

I'm looking for the result in "Distinct phone" row

Thanks for your time and help.

max.

Re: Count distinct row

MK_QSL — Fri, 24 Jun 2016 12:24:38 GMT

What is the result you are expecting?

Re: Count distinct row

maxime66 — Fri, 24 Jun 2016 12:29:03 GMT

Hi Manish,

looking for result in "Distinct phone" row

Re: Count distinct row

MK_QSL — Fri, 24 Jun 2016 12:33:58 GMT

Like this?

Re: Count distinct row

maxime66 — Fri, 24 Jun 2016 12:41:24 GMT

Result is good ,

but i would like not to touch my script ...

Re: Count distinct row

sunny_talwar — Fri, 24 Jun 2016 12:43:42 GMT

Non script way would be very complicated with if statements to check if the two columns have same value.

Re: Count distinct row

maxime66 — Fri, 24 Jun 2016 13:23:57 GMT

We have to ask Qlikview to create a special count

=countvalueline(distinct (Row1,Row2 ...) )

Re: Count distinct row

maxime66 — Fri, 24 Jun 2016 14:12:17 GMT

i found :

rangecount(expr1 [ , expr2, ... exprN ])

it's pretty much what i am looking for

but it seems that it doesn't work with Distinct

Re: Count distinct row

sunny_talwar — Fri, 24 Jun 2016 14:13:38 GMT

Exactly that's what the issue is.... For distinct you will need some if statements

Re: Count distinct row

maxime66 — Fri, 24 Jun 2016 15:07:09 GMT

Thanks anyway Sunny,

Let's Go for traditional IF(IF(IF(IF(IF

have a good weekend

Re: Count distinct row

johnw — Fri, 24 Jun 2016 22:35:21 GMT

count(distinct pick(match($Field,'Phone1','Phone2','Phone3'),[Phone1],[Phone2],[Phone3]))

count(distinct pick(valueloop(1,3),Phone1,Phone2,Phone3))

Re: Count distinct row

MK_QSL — Sat, 25 Jun 2016 07:04:23 GMT

John ‌ this is why we were missing you when you disappeared from QlikCommunity due to non-BI Project.

I learnt a new trick today.

Re: Count distinct row

johnw — Sat, 25 Jun 2016 20:49:11 GMT

Awww, thanks. I'd figured out the trick in the first expression not long ago when trying to figure out how to combine multiple fields into a single chart dimension. For that, it seems like you need to reference $Field to make the connection between the actual field and that field's values. Then after I posted I was looking at it here, where all we need are the field values, and the match() seemed to be doing nothing more than returning 1-3, which a valueloop() could do more simply. I was happy to see that it worked.

Still, if it was my application, I'd probably change the data model and do something more like you did. It's fun coming up with clever expressions to handle unusual requirements, but in the real world, I prefer the smarts be in the data model.

Re: Count distinct row

maxime66 — Mon, 27 Jun 2016 06:52:07 GMT

Hy John, you are the boss !!

Exactly what we need, simple and without script changes

Just for fun see what i should do without you :

=if(RangeCount(Phone1,Phone2,Phone3)=1 ,1,

if(RangeCount(Phone1,Phone2,Phone3)=2,

if(RangeMinstring(Phone1,Phone2,Phone3)<>

RangeMaxstring(Phone1,Phone2,Phone3),2,1),

if(RangeCount(Phone1,Phone2,Phone3 )=3,

if(Phone1<>Phone2 and Phone3<>Phone2 and Phone1<>Phone3 ,3,

if(Phone1=Phone2 and Phone3=Phone2,1,

if(Phone1=Phone2 and Phone3<>Phone2,2,

if(Phone1<>Phone2 and Phone3=Phone2,2,

if(Phone1=Phone3 and Phone3<>Phone2,2,0))))), 0)))

Re: Count distinct row

susovan — Mon, 27 Jun 2016 10:09:15 GMT

Hi,

You also try this,