topic Re: How To Display "duplicates" only in a List Box? in QlikView

How To Display "duplicates" only in a List Box?

Anonymous — Tue, 29 Apr 2014 18:23:35 GMT

Hello: I have the following Excel source:

DogId	DogName
1	Rex
2	Jack
3	Jack
4	Ginger
5	Clancy
6	Casey
7	Casey
8	Tommy

and the following script:

[DOGNAMES]:

LOAD DogId,

DogName

FROM

(ooxml, embedded labels, table is Sheet1);

and the following expression in a List Box:

=aggr(Only({1<DogName={"=Count(DogName)>1"}>}DogName), DogName

This does display Dog Names that have been duplicated. But it also displays the DogId of dogs that do not have duplicate names. My question is, "is it possible to display only the duplicates" ?

Thanks very much

David

Re: How To Display "duplicates" only in a List Box?

nagaiank — Tue, 29 Apr 2014 18:46:06 GMT

Try the following expression.

If(Aggr(Count({1}DogId),DogName)>1,DogName)

Re: How To Display "duplicates" only in a List Box?

MK_QSL — Tue, 29 Apr 2014 19:18:38 GMT

add below in your script after DOGNAMES table

Join (DOGNAMES)

Load

DogName,

COUNT(DogName) as DogNameCount

Resident DOGNAMES

Group By DogName;

Now write below in list box expression

IF(DogNameCount>1,DogName)

Re: How To Display "duplicates" only in a List Box?

Anonymous — Tue, 29 Apr 2014 19:20:27 GMT

Hi NagaianK:

I tried your suggestion but it still includes the non-duplicated values along with the duplicated values in the List box. I am trying to filter down the List box contents so that ONLY the duplicates show as would be the case with Jack and Casey. The non-duplicated items are a distraction to what I would like to accomplish.

Thanks

David

Re: How To Display "duplicates" only in a List Box?

lironbaram — Tue, 29 Apr 2014 19:32:29 GMT

hi use this

=aggr(only({<DogName={"=count(DogName)>1"}>}DogName),DogName)

Re: How To Display "duplicates" only in a List Box?

Anonymous — Tue, 29 Apr 2014 19:42:19 GMT

I guess I am not making my requirement clear. I only want the duplicated dog names to appear in the list box. For the example given, I should have only two lines: one for Jack and one for Casey. The lines that show the DogId and the - are for non-duplicated records.

Right now, everything shows up in "White" and yes it tells me which are duplicates but it also includes the non-duplicated Dog Ids.

Thanks

David

Re: Re: How To Display "duplicates" only in a List Box?

MK_QSL — Tue, 29 Apr 2014 19:46:39 GMT

Check enclosed file...

Re: How To Display "duplicates" only in a List Box?

Anonymous — Tue, 29 Apr 2014 19:48:30 GMT

Hi Manish:

Sorry I cannot read a foreign .qvw as I am using Qlikview Personal Edition. Can you send me the .qvw content that you referenced?

Thanks

David

Re: How To Display "duplicates" only in a List Box?

lironbaram — Tue, 29 Apr 2014 19:51:07 GMT

hi when using the expression above

i only see two names

does the list box display also id's

Re: How To Display "duplicates" only in a List Box?

MK_QSL — Tue, 29 Apr 2014 19:53:24 GMT

SCRIPT

=============================

DOGNAMES:

Load * Inline

[

DogId, DogName

1, Rex

2, Jack

3, Jack

4, Ginger

5, Clancy

6, Casey

7, Casey

8, Tommy

];

Join

Load

DogName,

COUNT(DogName) as DogNameCount

Resident DOGNAMES

Group By DogName;

============================

DogName List Box : Expression

=IF(DogNameCount>1,DogName)

DogId List Box : Expression

=IF(DogNameCount>1,DogId)

Re: Re: How To Display "duplicates" only in a List Box?

nagaiank — Tue, 29 Apr 2014 20:01:54 GMT

It shows only two entries as seen above.

Re: How To Display "duplicates" only in a List Box?

maxgro — Tue, 29 Apr 2014 20:58:44 GMT

=if(aggr(count(DogName),DogName)>1,DogName)

Re: How To Display "duplicates" only in a List Box?

Anonymous — Tue, 29 Apr 2014 21:18:37 GMT

Hi NagaianK:

Thanks very much. I had a couple of unnecessary steps in my work. When I took a closer look at your minimalist solution it worked perfectly.

Best regards

David

Re: How To Display "duplicates" only in a List Box?

MK_QSL — Wed, 30 Apr 2014 05:06:02 GMT

Believe me, everyone has given correct answer but may be there was some mistake happening your end.

Never mind, happy learning and Qliking!

Re: How To Display "duplicates" only in a List Box?

Anonymous — Wed, 30 Apr 2014 14:04:01 GMT

Hi Manish:

Yes, your solution is correct. I did have a couple of "extra" things going on and once I simplified it all everything works. I am so very grateful to the QlikView Community for their help. I am fast-becoming a huge QlikView fan and want to learn as much as I can about it. Your insights and those of the other respondents are a big part of that.

Thanks

David