topic Re: Create filter in QlikView

Create filter

ttal7878 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to create a filter in the UI.

IF(Category='A' and Age=0,Yes,

IF(Category<>'A' and Age=0,'No')

if the user selects 'Yes', I want to show all of the IDs that at least one of them match that same condition.

in the example attached, if the user selects 'Yes', the result that will be displayed:

Appreciates any help

Re: Create filter

techvarun — Thu, 27 Jul 2017 08:49:25 GMT

I fear that the category A and Age 9 won't fall on both Yes and Cases.

How do you want to handle the situation?

Re: Create filter

prieper — Thu, 27 Jul 2017 08:50:14 GMT

the attached is empty ...

Re: Create filter

prieper — Thu, 27 Jul 2017 08:55:25 GMT

You may create a listbox with calculation (although would prefer to handle this logic in the script), n'theless please check the conditions, as questioned by Varun or send an example with desired outcome

Re: Create filter

ttal7878 — Thu, 27 Jul 2017 09:03:25 GMT

Because the ID='A' has one line corresponding to the condition, I want to present all its lines,

Even though his other lines do not match the conditions

Re: Create filter

Miguel_Angel_Baeyens — Thu, 27 Jul 2017 09:06:00 GMT

This is one way of doing it, as mentioned, by using an expression in a listbox.

Obviously the second line is not selected if you click on Yes because you defined the If() for both Age = 0 (and the second line Age = 9) and Category = 'A'

Re: Create filter

Miguel_Angel_Baeyens — Thu, 27 Jul 2017 09:07:34 GMT

Then you need to redefine your condition, that could be an OR rather than an AND, that would show different values additionally as well

Re: Create filter

ttal7878 — Thu, 27 Jul 2017 09:08:33 GMT

It does not matter where to do the calculation, whether in script or in UI.

I explained Varun my desired outcome

It's like using P() function.

ID=P(Category='A' and Age=0).....

Re: Create filter

ttal7878 — Thu, 27 Jul 2017 09:14:35 GMT

This is not what I asked, I will try to explain my purpose to the query.

We want to know what happened with the IDs when they grow (when the age<>0),

and the relevant IDs are those whose category is 'A' and Age=0

Re: Create filter

Miguel_Angel_Baeyens — Thu, 27 Jul 2017 09:17:58 GMT

You likely need to use Aggr() and nested conditions, as you want to display Category='A' always, provided a value is selected (in this case, 'Yes'), regardless the other values in the record. Unless you have a more complex data set, in your example this is

If(Category = 'A', 'Yes', 'No')

Re: Create filter

tresB — Thu, 27 Jul 2017 09:18:52 GMT

Try something like:

=If(Aggr(Max(If(Category='A' and Age=0,1,

IF(Category<>'A' and Age=0,'2'))),Category)=1,'Yes',

If(Aggr(Max(If(Category='A' and Age=0,1,

IF(Category<>'A' and Age=0,'2'))),Category)=2,'No')

)

Re: Create filter

Miguel_Angel_Baeyens — Thu, 27 Jul 2017 09:21:30 GMT

I have to double post because I cannot edit

Re: Create filter

shivanisali — Thu, 27 Jul 2017 10:21:46 GMT

please look into the below attached qvw.

Re: Create filter

effinty2112 — Thu, 27 Jul 2017 11:17:22 GMT

Hi Tal Tal,

try this expression as a filter

=if(Aggr(sum(Match(Category,'A')*Match(Age,0)),ID)>=1,'Yes','No')

Picking 'Yes' gives:

ID	Category	Age
1	A	0
1	A	9

Picking 'No' gives:

ID	Category	Age
2	B	0
3	C	0
3	C	7
2	B	8

cheers

Andrew