topic Show Duplicates in QlikView

Show Duplicates

agsearle — Mon, 21 Dec 2009 23:22:26 GMT

I have loaded two tables into QV, that are linked by an 'Installation Number'.

In a table box the loaded data looks like this (There are other columns of data, but I've left them out for now):

INSTALLATION SERIAL NO
1 1234
2 2256
2 3455
3 5899
4 1425

From the table I need to show only those installations that have more than one serial number (ie installation number 2).

Any idea how I can do this?

Show Duplicates

Mon, 21 Dec 2009 23:29:38 GMT

Hi Andrew.

Here you are a possible solution.

Best regards.

Show Duplicates

Mon, 21 Dec 2009 23:30:45 GMT

In your expressions, use:

if(Count(SERIAL_NO) > 1, Sum(Sales))

That will make the value null when there is only one SERIAL NO and those won't be shown when using the default options for a table. You will need to use the if(Count piece on every expression in the table.

Show Duplicates

rwunderlich — Tue, 22 Dec 2009 06:39:00 GMT

If you have many expressions, you can instead do the filtering with a calculated dimension like:

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

-Rob

Show Duplicates

agsearle — Tue, 22 Dec 2009 16:51:22 GMT

Miguel, thanks for your suggestion but my data is taken from many tables, not just one, where your example works. I have many source tables and when the installation table is linked to the serial number table and a straight table box is constructed, you get multiple installations if there are more than one serial number per installation, and it's these ones that I'm looking for.

My schema is:

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/2656.SCREEN-SHOT.doc:550:0]

NMiller and Rob, I couldn't get your suggestions to work either sorry.

Show Duplicates

rwunderlich — Wed, 23 Dec 2009 01:15:23 GMT

Can you post a qvw?

-Rob