Aggr issue

[dhanu_today]

     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                       

[Gysbert_Wassenaar]

Please read these two documents:

• QlikCommunity Tip: How to get answers to your post?
• Preparing examples for Upload - Reduction and Data Scrambling.

[sbaldwin]

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;