Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

IF to exclude values in dimension/table

Hi

I am trying to make a pivot table that shows Names according to the below criteria:

Active = Y (so do not include N values)

Role = A or B (so do not include C values)

Type = N or V (so do not include E values)

The below if statement is what I used in the dimension but I still get values returning that include E values:

=if(Active='Y' and Role='A' or Role='B' and Type='N' or Type='V', Name)

Please can anyone assist?

Thanks!

1 Solution

Accepted Solutions

Re: IF to exclude values in dimension/table

Try this with parenthesis:

=If(Active='Y' and (Role='A' or Role='B') and (Type='N' or Type='V'), Name)

5 Replies

Re: IF to exclude values in dimension/table

Try this with parenthesis:

=If(Active='Y' and (Role='A' or Role='B') and (Type='N' or Type='V'), Name)

Not applicable

Re: IF to exclude values in dimension/table

Hi Sunny, unfortunately it didn't work. Are ther any other ways to do this?

Re: IF to exclude values in dimension/table

Not sure why it won't work, would you be able to share a sample?

Re: IF to exclude values in dimension/table

=if( Active='Y'  and match(Role,'A','B') and match(Type,'N','V') , Name)

Not applicable

Re: IF to exclude values in dimension/table

I was doing it wrong!

I have another question. How do I do a count distinct in set analysis Im trying:

=count({<Active={'Y'}, Role={'A','B'}>}NoOfDaysOver30)

But I want to count distinct on name so it groups all the names together and gives me a score of 1 per name over 30 but this would also need to be based on latest date I believe