Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
It shows only two entries as seen above.
Try the following expression.
If(Aggr(Count({1}DogId),DogName)>1,DogName)
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)
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
hi use this
=aggr(only({<DogName={"=count(DogName)>1"}>}DogName),DogName)
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
Check enclosed file...
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
hi when using the expression above
i only see two names
does the list box display also id's
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)