topic Duplicates in QlikView

Duplicates

dseelam — Fri, 18 Apr 2014 21:06:51 GMT

Hello,

I want to show 3 columns in a pivot table one with primary key, status and other with con numbers

I want to show only duplicate Primary keys with status=''Act" associated con numbers

Header 1	Header 2	Header 3
Primary key	status	Con Numbers
1	act	0
1	act	1
2	act	3
2	act	6

I don't want to show Primary key with out duplicates (which are normal)

Thanks In Advance

Re: Duplicates

stevedark — Fri, 18 Apr 2014 21:32:05 GMT

You have three dimensions for your pivot, and then a single expression. The expression would be something like:

If(count([Primary key]) > 1, count([Primary key]), null())

From the example you show it doesn't look like a pivot. If you use a straight table you can huge the expression column in the settings on the Presentation tab. It is not possible to his columns on a Pivot.

You could make it more efficient by adding a counter field in the load script, but you don't need to it performance is okay anyway.

Hope that helps,

Steve

Re: Duplicates

dseelam — Mon, 21 Apr 2014 17:47:09 GMT

I need to show all the primary keys with 2 different con numbers, No need of unique primary keys, looking for duplicates only

Re: Duplicates

bgerchikov — Mon, 21 Apr 2014 18:01:33 GMT

Hi Dinesh,

You should change chart type from Straight Table to Pivot Table in General tab.

Hope it will help

Re: Duplicates

Mon, 21 Apr 2014 18:16:44 GMT

Hi Dinesh,

Try this in your expression-with Primary Key, status and Con Numbers as your dimensions.

if (aggr(NODISTINCT count([Primary key]),status,[Primary key])>1,1,0)

Thanks

Re: Duplicates

dseelam — Mon, 21 Apr 2014 18:27:56 GMT

Hello Padmaja,

If I use this calculated dimension you provided

=if (aggr(NODISTINCT count([key]),[Status]='Active',[key])>1,1,0)

when I check on suppress nulls its not working

I am using 3 dimensions key status and con numbers

SO what I need is Keys with active status and duplicate rows when I add con number as 3rd dimension

Re: Duplicates

dseelam — Mon, 21 Apr 2014 18:29:01 GMT

Hello Boris

You are right But I need to show only duplicates no unique records with single row

Thanks

Re: Re: Duplicates

Mon, 21 Apr 2014 18:33:40 GMT

Hi Dinesh,

You need to use this as an expression not dimension. I have attached the qvw and the excel file. Is this what your are looking for?

Let me know if you need any help.

Thanks.

Re: Duplicates

dseelam — Mon, 21 Apr 2014 19:03:38 GMT

Wow It worked Padmaja, Just trying create the total number of keys as Text Object

Thank you

Dinesh

Re: Re: Re: Duplicates

dseelam — Tue, 22 Apr 2014 18:53:01 GMT

Hello Padmaja,

Concern

Hear is the sample QVW With Data I have

I need only row with primary key 566565

Re: Duplicates

Tue, 22 Apr 2014 18:57:29 GMT

Dinesh,

You need one row with primary key 566565 in the table? you don't need any other Primary keys?

Re: Re: Re: Duplicates

dseelam — Tue, 22 Apr 2014 19:00:28 GMT

Yes I need all Primary Keys with more than one Con Numbers

Thanks In Advance

Re: Re: Re: Re: Duplicates

Tue, 22 Apr 2014 19:27:16 GMT

Dinesh,

Like this? but this what you had originally rt?

Re: Re: Re: Duplicates

dseelam — Tue, 22 Apr 2014 20:41:23 GMT

You are absolutely right but when I try the same in my Original QVW It Is Hiding The Active

AND it is showing other Status like ho, bill etc...

Re: Duplicates

Tue, 22 Apr 2014 20:57:29 GMT

Can you upload your file? then I can see what the issue is.

Re: Re: Re: Duplicates

dseelam — Tue, 22 Apr 2014 21:18:18 GMT

Sorry It's A confidential File

Thanks

Re: Duplicates

Wed, 23 Apr 2014 16:54:07 GMT

Ok..can you upload dummy data which resembles your original data file. It will help...

Thanks

Re: Duplicates

dseelam — Wed, 23 Apr 2014 17:21:55 GMT

Hello Padmaja,

You are absolutely right in case you using data source-excel sheets because I tested using dummy data in excel,

I think there is some thing wrong in my data using from DWarehouse.

One more concern is how can you represent the above aggr Fun in text object for count of primary keys with act status and having 2 con numbers ?

Thanks