Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

how to determine the number of duplicate rows each record has

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.

1 Solution

Accepted Solutions
Partner
Partner

Re: how to determine the number of duplicate rows each record has

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

Regards,
Sergey
17 Replies
Partner
Partner

Re: how to determine the number of duplicate rows each record has

Hi,

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

Hope this helps.

Andrei

Partner
Partner

Re: how to determine the number of duplicate rows each record has

Hi

select dimension like uniqe key and expression

Count(1)

Regards,
Sergey
nizamsha
Valued Contributor II

Re: how to determine the number of duplicate rows each record has

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)

Partner
Partner

Re: how to determine the number of duplicate rows each record has

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

Not applicable

Re: how to determine the number of duplicate rows each record has

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

Not applicable

Re: how to determine the number of duplicate rows each record has

Sorry I don't get how I should type this

Partner
Partner

Re: how to determine the number of duplicate rows each record has

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

Partner
Partner

Re: Re: how to determine the number of duplicate rows each record has

Hi Rachel

PFA an example

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

Regards,
Sergey
Not applicable

Re: how to determine the number of duplicate rows each record has

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