topic Re: Need help with IF Statements in QlikView

Need help with IF Statements

Anonymous — Wed, 21 Dec 2016 13:32:53 GMT

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.

Re: Need help with IF Statements

sunny_talwar — Wed, 21 Dec 2016 13:38:41 GMT

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')

Re: Need help with IF Statements

Anonymous — Wed, 21 Dec 2016 13:52:04 GMT

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.

Re: Need help with IF Statements

Anonymous — Wed, 21 Dec 2016 14:57:40 GMT

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.

Re: Need help with IF Statements

Anonymous — Wed, 21 Dec 2016 15:18:55 GMT

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.

Re: Need help with IF Statements

Anonymous — Thu, 22 Dec 2016 06:09:37 GMT

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')