Filter out records from table based on conditions

[jchacko_rxsense]

I have one table:

TAB A

Task, Users, Enabled 
A,1,T
A,2,T
A,3,F
B,4,F
B,5,F
C,6,T
C,7,T
D,8,F
D,9,F

I need only those tasks having only disabled recipients i.e. Enabled = F. So in the above example only task B and D should be my output as that entire group has F.

[MayilVahanan]

Hi 

Try like below

Dim: Task

Exp: Only({<Enabled ={'F'}>}Enabled)

[Andrei_Cusnir · Accepted solution]

Hello,

@MayilVahanan's approach is usually a good practice. But it will give you also the task group A which has both Fs and Ts and as far as I understand you only want the tasks that have all their values for Enabled set as F's (Which is B and D only) 

Perhaps there are other ways to achieve similar outcome. However, here is one of the options that you can try:

1. Load the dataset

2. Add Task and Users as dimension and as measure the expression: if(Sum(Aggr(NODISTINCT COUNT({<Enabled={"F"}>}Enabled), Task )) = Sum(Aggr(NODISTINCT COUNT(Enabled), Task )), Only(Enabled), Null())

3. Go to Add-ons > Data handling  and un-check the option "Include zero values"

4. This will give you the outcome:

First expression counts all the Fs per group and the second expression counts all the values per group. If the two matches then it means that the group has only Fs and this is the one that you need. Otherwise, it has at least an T so you have to return Null().

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

[jchacko_rxsense]

Hey thanks for the quick response. Is it also possible to add Enabled with NULL as well. 
NULL or F in the entire group.

[jchacko_rxsense]

@Andrei_Cusnir Thanks for the response. The code you shared worked as expected. One enhancement though. 
Is it also possible to add Enabled with NULL as well. 

i.e. NULL or F in the entire group.

[jchacko_rxsense]

@Andrei_Cusnir  I am trying the following but it does not seem to work.
if(Sum(Aggr(NODISTINCT COUNT({<Enabled={"F"}>+<Enabled={"=Len(Enabled)=0"}>}Enabled),Task)) = Sum(Aggr(NODISTINCT COUNT(Enabled), Task )), Only(Enabled), Null())

[Andrei_Cusnir · Accepted solution]

Hello,

I am glad to hear that we are getting closer to the solution. The reason why it might not be working is because we have un-checked the option "Include zero values" in the Add-ons > Data handling section. For example if you modify the expression to:

if(
 Sum(Aggr(NODISTINCT COUNT({<Enabled={"F"}>}Enabled), Task )) 
 + 
 Sum(Aggr(NODISTINCT NullCount(Enabled), Task )) 
 = 
 Sum(Aggr( nodistinct COUNT(Enabled), Task )) +  Sum(Aggr(NODISTINCT NullCount(Enabled), Task )), 
 Only(Enabled), 
 Null()
)

This allows you to count all the all the Fs and Null() per task and if it is equal to the total count per task then it means that this task either has only Fs, only Null()s or only both, but it can't have Ts. However, if it is equal we return the actual "Enabled" value, which can be either Null() now or F. Therefore, the option "Include zero values" will now exclude those values from the view anyways.

To resolve this issue, you might use the following expression:

if(
 Sum(Aggr(NODISTINCT COUNT({<Enabled={"F"}>}Enabled), Task )) 
 + 
 Sum(Aggr(NODISTINCT NullCount(Enabled), Task )) 
 = 
 Sum(Aggr( nodistinct COUNT(Enabled), Task )) +  Sum(Aggr(NODISTINCT NullCount(Enabled), Task )), 
 if(IsNUll(Enabled), '-', 'F'),
 Null()
)

Outcome is:

This will check first if the value is Null and then if it is it will store a string "-" or the value "F". Please keep in mind that this solution is not very scalable and if you are going to have more requirement it might not be possible to achieve it. Also if you have a huge dataset it might reduce the performance.

I hope that this information is helpful.

[jchacko_rxsense]

@Andrei_Cusnir This looks great. But I need some time to test this out. But till now what I see is that this is working fine. 

Thanks a lot.