Count/IF Expression issue

[#1]

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)

[tresesco]

try putting single quotes around string 'Login' like:

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

[swuehl]

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.

If

=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.

[#4]

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)

[#5]

you could write an sql query..

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

[swuehl]

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.

[#7]

UserID (Person_addm2_key )

[swuehl · Accepted solution]

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 ....;

[somenathroy]

U may try :

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

Regards,

Som

[#10]

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)