topic Re: How to count Duplicate Id's only? in QlikView

How to count Duplicate Id's only?

Wed, 13 Nov 2013 10:23:38 GMT

Hi ,

I want to count only duplicate id's.

Ex:

In above 1,2,3 id are repeating so my output count should be:3

How we can calculate????

Re: How to count Duplicate Id's only?

tresB — Wed, 13 Nov 2013 10:27:03 GMT

Try something like:

=Count (If(Aggr(Count(id),id)>1,id) )

Edit: Parenthesis correction

Re: How to count Duplicate Id's only?

christian77 — Wed, 13 Nov 2013 10:28:44 GMT

Hi.

COUNT(

IF(

AGGR(COUNT(ID),ID)

,1)

Re: How to count Duplicate Id's only?

christian77 — Wed, 13 Nov 2013 10:30:31 GMT

that was fast.

I think your first count needs distinct.

Re: How to count Duplicate Id's only?

jagan — Wed, 13 Nov 2013 10:35:30 GMT

Hi Ashok,

Try this

=Sum(Aggr(If(Count(id) > 1, 1, 0), id))

Regards,

Jagan.

Re: How to count Duplicate Id's only?

Wed, 13 Nov 2013 10:52:01 GMT

tx for ur reply, but in above case i am getting difference value means

above expreesion works like this

=count(distinct id )-count(id)

I need count of id's where repeting more than once's

Re: How to count Duplicate Id's only?

er_mohit — Wed, 13 Nov 2013 10:55:15 GMT

try this in text object

=Count(Aggr(NODISTINCT Count(id),id))

Re: How to count Duplicate Id's only?

swuehl — Wed, 13 Nov 2013 10:57:14 GMT

Or try

=count({<id = {"=count(id)>1"}>} distinct id)

Re: How to count Duplicate Id's only?

tresB — Wed, 13 Nov 2013 10:59:18 GMT

Nope! I guess you are missing somewhere. Because that is already aggregated. Isn't it so?

Re: How to count Duplicate Id's only?

Mon, 20 Jan 2014 17:53:41 GMT

Can anybody tell me how to do that in script?

Re: How to count Duplicate Id's only?

jagan — Tue, 21 Jan 2014 02:34:20 GMT

Hi Sampath,

Use Group by in the Load statement

Data:

LOAD

ID,

FROM Table;

INNER JOIN(Data)

LOAD

WHERE IDCount > 1;

LOAD

ID,

Count(DISTINCT ID) AS IDCount

RESIDENT Data

GROUP BY ID;

Hope this helps you.

Regards,

Jagan.