Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

danny4202001
Contributor

Duplicates

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 1Header 2Header 3
Primary key statusCon Numbers
1act0
1act1
2act3
2act6

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

Thanks In Advance

1 Solution

Accepted Solutions
Not applicable

Re: Re: Duplicates

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.

17 Replies
MVP
MVP

Re: Duplicates

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

danny4202001
Contributor

Re: Duplicates

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

bgerchikov
Contributor III

Re: Duplicates

Hi Dinesh,

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

Hope it will help

Not applicable

Re: Duplicates

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

danny4202001
Contributor

Re: Duplicates

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

danny4202001
Contributor

Re: Duplicates

Hello Boris

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

Thanks

Not applicable

Re: Re: Duplicates

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.

danny4202001
Contributor

Re: Duplicates

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

Thank you

Dinesh

danny4202001
Contributor

Re: Re: Re: Duplicates

Hello Padmaja,

Concern

Hear is the sample QVW With Data I have

I need only row with primary key 566565

Community Browser