Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jchacko_rxsense
Contributor III
Contributor III

Filter out records from table based on conditions

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.

Labels (5)
2 Solutions

Accepted Solutions
Andrei_Cusnir
Support
Support

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. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂

View solution in original post

Andrei_Cusnir
Support
Support

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.

 

 

 

 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂

View solution in original post

7 Replies
MayilVahanan

Hi 

Try like below

Dim: Task

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

Thanks & Regards,
Mayil Vahanan R
Andrei_Cusnir
Support
Support

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. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
jchacko_rxsense
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

@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
Contributor III
Contributor III
Author

@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
Support
Support

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.

 

 

 

 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
jchacko_rxsense
Contributor III
Contributor III
Author

@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.