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.

[crusader_]

Hi,

Try to use count(any field) ... group by all other fields.

Hope this helps.

Andrei

[SergeyMak]

Hi

select dimension like uniqe key and expression

Count(1)

[nizamsha]

in a list box  u can write the expression and check how many times that record is coming

keep two listbox 1.count (distint urfieldname) 2. count(urfieldname)

[felcar2013]

hi, i did not try this but, you can

1 sort your table ( you can Count the number of records)

2 create a Composite key with all columns (if they are not so many)

3 use autonumber and create a numeric key, same records will receive the same numeric key

4 use previous function and compare if the sorted keys are the same and flag it, then you can recognize the duplicates

[#6]

Where should I type this? I cant seem to type in edit script.

[#7]

Sorry I don't get how I should type this

[crusader_]

If you want to add this coloumn permanently, just make like this:

ur_tbl:

Load

     field1

     ,  field 2

     ,  field 3

     ,  'key' as ArtificialKey

from (...);

noconcatenate

new_ur_tbl:

LOAD field 1

     , field 2

     , field 3

     , count(ArtificialKey) as Qty

Resident ur_tbl

Group By

field 1

     , field 2

     , field 3 ;

drop table ur_tbl;

But if you want use it in chart only better use Sergey's approach, just count(1)

Andrei

[SergeyMak]

Hi Rachel

PFA an example

In this example the row with Id=1 repeats two times

[#10]

Sorry but I'm not able to open the file actually...could you maybe type out the example? thanks:)