Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Need help with IF Statements

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

5 Replies
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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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