Need help with IF Statements

[Anonymous]

I need help with 2 IF statements.

First One

Here is the logic:

[QueryID1 = 'RESP.1' OR 'RESP.2' OR 'RESP.3' AND Response1=11

OR QueryID1='RESP.4' and Response1=4]

AND (QueryEducation='TEACH.1' and Response3=34)

Then “Yes” Otherwise “NO”

Here is my statement that is not pulling the results correctly:

=IF((((QueryID1='RESP.1'OR QueryID1='RESP.2' OR QueryID1='RESP.3') and Response1=11)OR (QueryID1='RESP.4' and Response1=4)) and (QueryEducation='TEACH.1' and Response3=34), 'YES','NoMatch')

Second One

No Education requirement

[QueryID1 = 'RESP.1' OR 'RESP.2' OR 'RESP.3' AND Response1=11

OR QueryID1='RESP.4' and Response1=4]

Then “Yes” Otherwise “NO”

Here is my statement that is not pulling the results correctly:

=IF((((QueryID1='RESP.1'OR QueryID1='RESP.2'OR QueryID1='RESP.3') and Response1=11) OR (QueryID1='RESP.4' and Response1=4)), 'YES','NoMatch')

Currently, when an account has QueryID1='RESP.3' and Response1=4, it still gets selected. However, it should only get selected if the Response was 11 for RESP.3.

Thank you.

[sunny_talwar]

May be this:

First One

=If(((Match(QueryID1, 'RESP.1', 'RESP.2', 'RESP.3') and Response1 = 11) or (QueryID1 = 'RESP.4' and Response1 = 4)) and (QueryEducation = 'TEACH.1' and  Response3 = 34), 'YES', 'NoMatch')

Second One

=If((Match(QueryID1, 'RESP.1', 'RESP.2', 'RESP.3') and Response1 = 11) or (QueryID1 = 'RESP.4' and Response1 = 4), 'YES', 'NoMatch')

[Anonymous]

It is still having the same issue. Currently, when an account has QueryID1='RESP.3' and Response1=4, it still gets selected when I click "YES". However, it should only get selected if the Response was 11 for RESP.3. It's been driving me crazy since yesterday.

[Anonymous · Accepted solution]

Hello,

you need to add an aggr() function if you are using this expression as a dimension. It could look like this:

If(Aggr(Only({<QueryID1={'RESP.1','RESP.2','RESP.3'}>} Response1),Account_ID) = 11 or
Aggr(Only({<QueryID1={'RESP.4'}>} Response1),Account_ID) = 4,'YES','NO')

Basically, you need to teach QlikView according to which field it should Aggregate. In the sample application below I tried to create your Scenario. I hope it could help.

[Anonymous]

Great. That worked for the second query. Do you mind to please help me with the first query as well where there is teaching education query included? Thanks so much.

[Anonymous]

No Problem! Since there is an"AND-relationship" in your definition, it'll be enough to add "QueryEducation" and "Response3" into the set Analysis. The formula will then look like this:

  If(Aggr(Only({<QueryID1={'RESP.1','RESP.2','RESP.3'},QueryEducation={'TEACH.1'}, Response3={'34'}>} Response1),Account_ID) = 11 or
Aggr(Only({<QueryID1={'RESP.4'},QueryEducation={'TEACH.1'}, Response3={'34'}>} Response1),Account_ID) = 4,'Yes','No Match')