Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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')
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.
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.
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.
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')