topic Re: How to list all duplicated record ? in QlikView

How to list all duplicated record ?

paulyeo11 — Thu, 30 Oct 2014 04:36:17 GMT

Hi All

The reasons is duplicated record , create issue on display price list as there are 2 record QV does not know which one to display. so i need to find out how many duplicated record in my

database , and i need to manually delete those duplicated record.

I like to know how to add one more expression at the enclosed table , which will indicate all duplicate record with "1" ?

Below are the duplicated record :-

FC9001-0010

FC9002

FC9004

FC9011

Paul

Re: How to list all duplicated record ?

jyothish8807 — Thu, 30 Oct 2014 04:48:43 GMT

Hi Paul,

Create a Rowno() in your script so it will create a unique primary key for all the rows, then you can identify your duplicates easily.

Eg:

Load

Rowno() as Sino,

from <>;

Regards

Re: How to list all duplicated record ?

tresB — Thu, 30 Oct 2014 04:48:45 GMT

How do you decide the duplicacy of records, PART_NO coming more that once? If so, try list box expression:

Aggr(if(Count(PART_NO_)>1,PART_NO_),PART_NO_)

and it gives much more part_nos than you thought.

Re: How to list all duplicated record ?

perumal_41 — Thu, 30 Oct 2014 04:56:25 GMT

Hi Paul,

PFA,I hope it help to u

Re: How to list all duplicated record ?

Kushal_Chawda — Thu, 30 Oct 2014 05:03:33 GMT

I think you have two different product group for FC9004 Part_no. that is why it is getting two row in your chart.

Re: How to list all duplicated record ?

er_mohit — Thu, 30 Oct 2014 05:11:09 GMT

Try this expression

aggr(NODISTINCT RowNo(),PART_NO_)

Re: How to list all duplicated record ?

paulyeo11 — Thu, 30 Oct 2014 05:23:31 GMT

Hi Mohit

When i add below into my expression :-

aggr(NODISTINCT RowNo(),PART_NO_)

I am not able make the table list all duplicate record , like FC9004. any advise ?

Paul

Re: How to list all duplicated record ?

paulyeo11 — Thu, 30 Oct 2014 05:24:24 GMT

Hi Kush,

Yes you are right , that is my problem on my raw data . now i need to list them out , any idea ?

Paul

Re: How to list all duplicated record ?

paulyeo11 — Thu, 30 Oct 2014 05:29:09 GMT

Hi Tres

Thank you. But how come it never list FC9004 ? As FC9004 have duplicated record .

Paul

Re: How to list all duplicated record ?

paulyeo11 — Thu, 30 Oct 2014 05:30:33 GMT

Hi Peruma

Thank you very much , your answer very similar to Tres. But why FC9004 is not listed ?

Paul

Re: How to list all duplicated record ?

paulyeo11 — Thu, 30 Oct 2014 05:31:49 GMT

Hi Jyothish

I try to use expression to do it first , till i have no choice , then i will try load script.

Paul

Re: How to list all duplicated record ?

jyothish8807 — Thu, 30 Oct 2014 05:39:52 GMT

Hi Paul,

I have a doubt, why do you want to delete the duplicates since the product group associated with Part no is different?

Use Sum(Listprice) as expression.

Regards

Re: How to list all duplicated record ?

jagan — Thu, 30 Oct 2014 05:46:20 GMT

Hi,

Try like this

Chart : Straight Table

Dimension : PART_NO_

Expression: =If(Count(PART_NO_) > 1, Count(PART_NO_))

or if you have part_no with duplicate product groups

=If(Count(DISTINCT [Product Group]) > 1, Count(DISTINCT [Product Group]))

Regards,

jagan.

Re: How to list all duplicated record ?

perumal_41 — Thu, 30 Oct 2014 05:47:23 GMT

Hi Paul,

In my application listing FC4009.

PFA

Re: How to list all duplicated record ?

Kushal_Chawda — Thu, 30 Oct 2014 05:55:25 GMT

Please see the attached.

I have created one table for Duplicate records

Re: How to list all duplicated record ?

paulyeo11 — Thu, 30 Oct 2014 06:04:32 GMT

Hi Jagan

Your answer , so far it the best .Because you can display duplicated rec on table.

Thank

Paul

Re: How to list all duplicated record ?

paulyeo11 — Thu, 30 Oct 2014 06:07:38 GMT

Hi Perumai

Okay after i change the presentation Tab number of line = 9999 , i can view FC9004.

Thank you.

Paul

Re: How to list all duplicated record ?

paulyeo11 — Thu, 30 Oct 2014 06:09:16 GMT

Hi Kush

Thank you very much , your also working.

Paul

Re: How to list all duplicated record ?

paulyeo11 — Thu, 30 Oct 2014 06:11:51 GMT

Hi Jyothish

you ask a very impt question , because it is very hard for me to remove manually all duplicated record. as i have too many duplicated record.

can i show you my actual QV doc , and you can advise me how to modify my expression , so that FC9004 can be display out ?

Paul