topic Re: Aggr issue in QlikView

Aggr issue

dhanu_today — Tue, 29 Oct 2013 05:33:46 GMT

Hi All,

The req is like;

We ahev to remove the records at dimension level and count level with below sample data.

ID RM count

101 345 0

102 1001 1

103 345 0

104 786 0

104 789 0

109 505 1

109 - 0

The req is if the multiple id's(101,103) allocated to multiple RM's we have to remove that data and not consider in the count level.The same as if the multiple RM's allocated(786,789) to same Id we have to remove that data and not consider in the count.If in case if the Id is having multiple RM's and any one of that RM's contan blank(like 109 -) then we have to count the non blank record(like 505).

Thanks,

Dhanu

Re: Aggr issue

Gysbert_Wassenaar — Tue, 29 Oct 2013 10:56:47 GMT

Please read these two documents:

Re: Aggr issue

sbaldwin — Wed, 30 Oct 2013 14:29:47 GMT

Hi i would create this count field in your script with something like this:

temp:

load * inline [

ID,RM

101,345

102,1001

103,345

104,786

104,789

109,505

109];

join (temp)

load ID,count(distinct RM) as RM_count Resident temp where RM <> '' and not isnull(RM) AND ID <> '' and not isnull(ID) group by ID;

join(temp)

load RM,count(distinct ID) as ID_count Resident temp where RM <> '' and not isnull(RM) AND ID <> '' and not isnull(ID) group by RM;

data:

load ID,RM,if(RM_count = 1 and ID_count = 1,1,0) as counter Resident temp;

drop table temp;