how to determine the number of duplicate rows each record has

[#1]

Hi, how do I determine the number of duplicate rows each record has? Do I use recno function? I have many records that have many duplicates and also records that only appear once, and I want to create another column that displays the number of duplicate rows for each record. Thanks.

[anbu1984]

Can you show sample input and expected output?

[its_anandrjs]

You can adopt this small example in load script see the sample script

Source:

LOAD * Inline

[

ID

A

A

A

B

C

C

C

D

D

D

E

];

CountTable:

LOAD

ID,

Count(ID) as Count

Resident Source

Group By ID;

Info:

LOAD

ID,

Count,

If(Count > 1,'Duplicate Records','No Duplicates') as Details

Resident CountTable;

DROP Table CountTable;

[#4]

In load script you can write the statement as A&B(A and B are the combination field name) in load and in chart expresion u can directly use count(A&B).

Load A,

B,

A&B as comb

from test.xls;

and in Chart expresion tab:

count(comb)

[#5]

input table

date	site	person
Apr-14	AQ	Rachel
May-14	FT	gklim
May-14	FT	gklim
Apr-14	AQ	Rachel
Apr-14	AQ	Rachel
Apr-14	HU	ACH

output table:

date	site	person	Count
Apr-14	AQ	Rachel	3
Apr-14	HU	ACH	1
May-14	FT	gklim	2

[SergeyMak · Accepted solution]

Select  date, site, person as dimensions of straight table, and write expression Count(date)

[#7]

Is straight table =table box? I dont have straight table in my options...

[SergeyMak]

it is not table box

You should add chart and on the first screen select Straight table - left, bottom

[#9]

Thanks so much!