Count duplicates in column

[MT4T]

Hello,

I try to count duplicate "appearance" among NRMA according to SN.

In table below i have eleven cases of NRMA, four of them have unique SN and seven are duplicates.

I need a function which returns "7" as a sum of duplicate values.

NRMA	SN	Appearance
RMA0001	28344400901449	3
RMA0002	27421901163	1
RMA0003	40116491002153	1
RMA0004	91044232	2
RMA0005	38128280108960	2
RMA0006	98101670804483	1
RMA0007	91044233	1
RMA0008	28344400901449	3
RMA0009	91044232	2
RMA0010	38128280108960	2
RMA0011	28344400901449	3

[justISO]

Count(distinct SN) ?

[MT4T]

=Count(distinct[SN]) returns the unique appearance of SN. So, i need this in the opposite version.

[justISO]

so, as I understand, you need to count 'unique' values, or, just sum where 'Appearance'=1:

sum( if( (aggr(nodistinct count(SN), SN))>1,1,0))

where part aggr(nodistinct count(SN), SN) you can change to object [Appearance] if you have it already calculated it in script.

Or if you want count NRMA with 'unique' SN:

count(distinct (if( (aggr(nodistinct count(SN), SN))>1,NRMA)))

[ggijben]

Can you try:  

COUNT(AGGR(IF(Appearance>1, NRMA), NRMA))

[MT4T]

No i did not solve in script right now. Sample data was from excel file, sorry for misleading, I am just novice who discover Qlik possibilities with old excel habits.

I need to sum "appearance" which is bigger than "1". But on raw data without this "appearance" column.

So there should be 7 duplicate SN and 4 unique SN. Function "=Count(distinct[SN]) " properly counts unique. But i need to count dupplicates.

NRMA	SN
RMA0001	28344400901449
RMA0002	27421901163
RMA0003	40116491002153
RMA0004	91044232
RMA0005	38128280108960
RMA0006	98101670804483
RMA0007	91044233
RMA0008	28344400901449
RMA0009	91044232
RMA0010	38128280108960
RMA0011	28344400901449

[ggijben · Accepted solution]

Ah okay, then try this one: 

COUNT(distinct AGGR(IF(Count(SN)>1, NRMA), NRMA))