Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
davidbalmer
Contributor III
Contributor III

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)

davidbalmer
Contributor III
Contributor III
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
Partner

hi use this

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

davidbalmer
Contributor III
Contributor III
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...

davidbalmer
Contributor III
Contributor III
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
Partner

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)