topic Re: How to Calculate Duplicate % in QlikView

How to Calculate Duplicate %

Anonymous — Fri, 21 Jul 2017 05:51:52 GMT

Gud Morning ,

Today I was going to calculate Duplicate % region wise,

but I stucked . Cany one please help me to come out from this problem.

Situation : Total Record = 267052

Duplicate Record = 20322

Now want to know how much duplicate % I have in each region.

Region	Total	Total Number of Duplicate Record	Duplicate region wise	Duplicate %
Americas	61048	20322	3807	18.73%
APAC	23957	20322	1195	5.88%
Emerging Markets	46519	20322	3346	16.46%
Europe	89447	20322	7037	34.63%
France	46080	20322	4937	24.29%

Can any one pls try ?

Now I want 20322 similary in text box.

or Straight table in qlick view similar like above table.

Re: How to Calculate Duplicate %

Anil_Babu_Samineni — Fri, 21 Jul 2017 06:03:59 GMT

I think you need TOTAL Keyword within the Count aggregation, Can you share the qvw file

Re: How to Calculate Duplicate %

Anonymous — Fri, 21 Jul 2017 06:06:00 GMT

Hi, Anil Babu

Please use the file attched in qlick view.

Re: How to Calculate Duplicate %

Anil_Babu_Samineni — Fri, 21 Jul 2017 06:15:54 GMT

The reason i ask you is, Your excel file corrupted. Can you load the excel and share the qvw?

Re: How to Calculate Duplicate %

Fri, 21 Jul 2017 06:26:25 GMT

Use straight table with dimension Region.

For Total expression is =count(GID)

For Total number of duplicate records =Count(TOTAL GID)-Count(TOTAL DISTINCT GID)

for duplicate region wise =Count(GID)-Count(DISTINCT GID)

for % =(Count(GID)-Count(DISTINCT GID))/(Count(TOTAL GID)-Count(TOTAL DISTINCT GID))

Hope this helps

Regards

Krishna KS

Re: How to Calculate Duplicate %

prma7799 — Fri, 21 Jul 2017 06:26:29 GMT

PFA...

Re: How to Calculate Duplicate %

md_qlikview — Fri, 21 Jul 2017 07:02:53 GMT

Hi,

PFA File for your reference.

Re: How to Calculate Duplicate %

Anonymous — Fri, 21 Jul 2017 07:35:25 GMT

Please share the expression.

As I don't have licence version.

Thanks,

Re: How to Calculate Duplicate %

prma7799 — Fri, 21 Jul 2017 07:36:48 GMT

1.Count(GID)

2.((count(TOTAL {<Region>} GID)-count( DISTINCT TOTAL {<Region >} GID)))

3.Count(GID)-Count(DISTINCT GID)

4.Column(3)/Column(2)

Re: How to Calculate Duplicate %

prma7799 — Fri, 21 Jul 2017 07:38:47 GMT

Hi Bijay,

for Duplicate %

Re: How to Calculate Duplicate %

Kushal_Chawda — Fri, 21 Jul 2017 07:54:21 GMT

May be this

=Sum(aggr(Count(GID3) - Count(distinct GID3), Region))

Re: How to Calculate Duplicate %

Anonymous — Fri, 21 Jul 2017 07:57:06 GMT

Aggr function will not work here, as it gives only one outcome as result

Re: How to Calculate Duplicate %

Kushal_Chawda — Fri, 21 Jul 2017 07:59:50 GMT

have you tried it? what is the output you are getting?

Re: How to Calculate Duplicate %

Anonymous — Fri, 21 Jul 2017 17:09:21 GMT

yes

Re: How to Calculate Duplicate %

Anonymous — Wed, 09 Aug 2017 18:54:51 GMT

If your using list box, you have to check frequency check box in list box properties

Re: How to Calculate Duplicate %

Anonymous — Thu, 10 Aug 2017 03:05:09 GMT

True,

But it was given only to test the formula.