Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

Hello:  I have the following Excel source:

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

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

1 Solution

Accepted Solutions
nagaiank
Specialist III
Specialist III

Capture.PNG.png

It shows only two entries as seen above.

View solution in original post

14 Replies
nagaiank
Specialist III
Specialist III

Try the following expression.

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

MK_QSL
MVP
MVP

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)

Anonymous
Not applicable
Author

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

lironbaram
Partner - Master III
Partner - Master III

hi use this

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

Anonymous
Not applicable
Author

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

MK_QSL
MVP
MVP

Check enclosed file...

Anonymous
Not applicable
Author

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

lironbaram
Partner - Master III
Partner - Master III

hi when using the expression above

i only see two names

does the list box display also id's

MK_QSL
MVP
MVP

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)