topic Re: Double Counting With Aggregation with overlap in dimension values in QlikView

Double Counting With Aggregation with overlap in dimension values

gopal5879 — Thu, 25 Feb 2021 20:42:08 GMT

Hello folks, Need some help to fix an issue in production. Any help would be greatly appreciated.

I am trying to figure to suppress double counting of a dimension value. Attaching screen shot explaining the issue.

Have the source data loaded as below:

Soource Data :

pid	tin
p1	t1
p1	t2
p2	t1
p3	t2
p4	t2
p5	t3
p6	t3
p7	t3

The requirement is for each of pid, RelatedPIDS should be computed via tin and through the tin against pid get other pids and show as count including current pid. I am double counting for pid p1 since it shared to tins t1 and t2.

using the expression to compute RelatedPIDS field.

sum( Aggr(NODISTINCT count( distinct {1<tin={"*"} >}pid),tin))

For pid p1, it is double counting since it has overlap with tin t1 and t2. I am getting count of 5 instead of 4 since p1 is counted twice which is shared with tin t1 and t2.

Can some one suggest how to suppress double count? Example data attached below.

Re: Double Counting With Aggregation with overlap in dimension values

PriyankaShivhare — Fri, 26 Feb 2021 04:53:00 GMT

mapSource:

Mapping Load
tin,
concat(pid,',') as t
group by tin;

Load * Inline [
pid, tin
p1, t1
p1, t2
p2, t1
p3, t2
p4, t2
p5, t3
p6, t3
p7, t3

];

NoConcatenate
s1:
Load pid,
subfield(t1,',') as t2;
Load pid,
tin,
ApplyMap('mapSource',tin,null()) as t1;
Load * Inline [
pid, tin
p1, t1
p1, t2
p2, t1
p3, t2
p4, t2
p5, t3
p6, t3
p7, t3

];

In the front end

dim:Pid

measure: count(distinct t2)

Re: Double Counting With Aggregation with overlap in dimension values

Saravanan_Desingh — Fri, 26 Feb 2021 14:02:59 GMT

Try this,

tab1: LOAD * INLINE [ pid, tin p1, t1 p1, t2 p2, t1 p3, t2 p4, t2 p5, t3 p6, t3 p7, t3 ]; Left Join(tab1) LOAD tin, pid As pid2 Resident tab1;

Re: Double Counting With Aggregation with overlap in dimension values

Saravanan_Desingh — Fri, 26 Feb 2021 14:03:24 GMT

Output:

Re: Double Counting With Aggregation with overlap in dimension values

gopal5879 — Mon, 01 Mar 2021 19:32:14 GMT

Hello Saran,

My approach using set analysis with nested aggregation did not work

Your solution works, Many thanks. Appreciate your time and timely help.

Re: Double Counting With Aggregation with overlap in dimension values

gopal5879 — Mon, 01 Mar 2021 19:33:55 GMT

Hello Priyanka,

My approach using set analysis with nested aggregation did not work

Your solution works, Many thanks to you and Saran. Appreciate your time and timely help.