Count distinct row

[maxime66]

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.

[johnw · Accepted solution]

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

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

[MK_QSL]

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

I learnt a new trick today.

[johnw]

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.

[maxime66]

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)))

[susovan]

Hi,

You also try this,