topic Re: Count/IF Expression issue in QlikView

Count/IF Expression issue

Thu, 07 Nov 2013 11:44:49 GMT

Hi All,

I am trying to find the count of those distinct UserID's who have login count more than one.

The below expression is nt fetching the correct result.

Any help on this please

=Count({< UserID= {"=count({<PG_NM = {Login}>} UserID)>1 "} >} distinct UserID)

Re: Count/IF Expression issue

tresB — Thu, 07 Nov 2013 12:04:52 GMT

try putting single quotes around string 'Login' like:

=Count({< UserID= {"=count({<PG_NM = {'Login'}>} UserID)>1 "} >} distinct UserID)

Re: Count/IF Expression issue

swuehl — Thu, 07 Nov 2013 12:08:11 GMT

Well, that's hard to answer without knowing your data model and data.

You'll need to add more information about the data itself, the context you are using this expression in and the expected outcome.

=count({<PG_NM = {Login}>} UserID)

used in a straight table with dimension UserID returns correct results for Login count, your expression should return the correct number e.g. in a text box.

Re: Count/IF Expression issue

Thu, 07 Nov 2013 12:21:16 GMT

Actually Login has double quotes in it "Login"{in DB}

When i am trying that its throwing an error

=Count({< UserID= {"=count({<PG_NM = {' " Login" '}>} UserID)>1 "} >} distinct UserID)

Re: Count/IF Expression issue

Thu, 07 Nov 2013 12:29:31 GMT

you could write an sql query..

select count(*) as num_of_logins,userid,login from <table> group by userid,login having count(*)>1;

Re: Count/IF Expression issue

swuehl — Thu, 07 Nov 2013 12:36:06 GMT

What kind of error do you get using the single quotes in your field modifier value list? Seems that you are using additional spaces, but something like

=Count({< UserID= {"=count({<PG_NM = {'"Login"'}>} UserID)>1 "} >} distinct UserID)

works for me.

Re: Count/IF Expression issue

Thu, 07 Nov 2013 12:43:42 GMT

UserID (Person_addm2_key )

Re: Count/IF Expression issue

swuehl — Thu, 07 Nov 2013 12:55:50 GMT

Ok, I missed that you are using the field modifier within another field modifier search expression.

Right, this could makes things more complicated.

Easiest solution would be to strip the double quotes when reading from your DB:

LOAD

purgechar(PG_NM, '"') as PG_NM,

....

FROM ....;

Re: Count/IF Expression issue

somenathroy — Thu, 07 Nov 2013 12:56:22 GMT

U may try :

if(Aggr(Count({<PG_NM={'"Login"'}>}PG_NM),UserID) >1,Aggr(Count({<PG_NM={'"Login"'}>}PG_NM),UserID))

Regards,

Som

Re: Count/IF Expression issue

Thu, 07 Nov 2013 14:06:45 GMT

Hey Thanks swuehl!!

The expression worked for me .

I Used purgechar(PG_NM, '"') as PG_NM and

then gave the expression as

=Count({< UserID= {"=count({<PG_NM = {'Login'}>} UserID)>1 "} >} distinct UserID)

Re: Count/IF Expression issue

Thu, 07 Nov 2013 14:07:11 GMT

Thanks All.

I got the output.