topic if and 'and condition' in QlikView

if and 'and condition'

Thu, 19 Feb 2015 19:57:31 GMT

hi,

i am using if and 'and condition'. when i ise 2 and condition, output comes correct. howevr, when i use the 3rd condition, i dont get any output

=if(GetFieldSelections(Region)='EMEA' AND GetFieldSelections(Domain)='MOPS' AND GetFieldSelections(Domain)='Reports',3.67)

help on this would be appreciated

Re: if and 'and condition'

Anonymous — Thu, 19 Feb 2015 20:02:43 GMT

You are saying if the Domain field selection equals MOPS and it also equals Reports. Are you meaning to do this?

Re: if and 'and condition'

Thu, 19 Feb 2015 20:04:57 GMT

yes, i have 2 listboxes...if i select both mops and reports from my second listbox and select only 'emea' from my first listbox, then i should get the value 3.67

Re: if and 'and condition'

maxgro — Thu, 19 Feb 2015 20:06:16 GMT

any output because

Domain can't be at the same time MOPS and Reports

Re: if and 'and condition'

Thu, 19 Feb 2015 20:08:16 GMT

then how to rewrite the expression, i have selected 2 values(mops, reports) from the 2nd listbox and 1 value(emea) from the 1st listbox....

Re: if and 'and condition'

maxgro — Thu, 19 Feb 2015 20:08:28 GMT

add a textbox

with =GetFieldSelections(Domain)

Re: if and 'and condition'

Anonymous — Thu, 19 Feb 2015 20:08:58 GMT

OK so when you use GetFieldSelections you'll want to do this: WildMatch(GetFieldSelections(Domain),'*MOPS*','*Reports*')

Re: if and 'and condition'

Thu, 19 Feb 2015 20:12:10 GMT

thanks a lot..it worked

Re: if and 'and condition'

Thu, 19 Feb 2015 20:20:15 GMT

there is another probm here. i m using nested if. so if i write the first condition and only mops is selected it returns vale as 10 instead of 8. how to fix this?

if(GetSelectedCount(Region)=0 AND WildMatch(GetFieldSelections(Domain),'*MOPS*','*Reports*') ,10.00,
if(GetSelectedCount(Region)=0 AND GetFieldSelections(Domain)='MOPS',8.00,

Re: if and 'and condition'

Anonymous — Thu, 19 Feb 2015 20:25:55 GMT

Looks like you are running into an error in the order of operations... The first statement says that if the Domain select has Mops then it's true... reverse the statements and it will work.

Re: if and 'and condition'

Thu, 19 Feb 2015 20:34:47 GMT

wild match is acting as an or which is not solving my issue., i am looking for an "and".....

if(GetSelectedCount(Region)=0 AND WildMatch(GetFieldSelections(Domain),'*MOPS*','*Reports*') ,10.00,
if(GetSelectedCount(Region)=0 AND GetFieldSelections(Domain)='MOPS',8.00,

if both mops and reports are selectted then output should be 10 and if only mops is selected then output should be 8.

Re: if and 'and condition'

Anonymous — Thu, 19 Feb 2015 20:37:45 GMT

Yep so you'll have to move the expression around a bit...

if(GetSelectedCount(Region)=0 AND GetFieldSelections(Domain)='MOPS',8.00,

if(GetSelectedCount(Region)=0 ANDWildMatch(GetFieldSelections(Domain),'*MOPS*','*Reports*') ,10.00,

Re: if and 'and condition'

Thu, 19 Feb 2015 20:41:26 GMT

lol..i believe i had to tell the entire story rather thn breaking it ,here is the complete set and problem

if(GetSelectedCount(Region)=0 AND GetFieldSelections(Domain)='MOPS',8.00,

if(GetSelectedCount(Region)=0 ANDWildMatch(GetFieldSelections(Domain),'*MOPS*','*Reports*') ,10.00,

if(GetSelectedCount(Region)=0 ANDWildMatch(GetFieldSelections(Domain),'*WEB*','*Reports*') ,13.00,

NOW if value web and reports are selected, it gives the output as 10 instead of 13. how to fix this.

really appreciate your assistance and patience.

Re: if and 'and condition'

Anonymous — Thu, 19 Feb 2015 20:51:07 GMT

You'll have to add some logic to say that it's NOT WildMatch(GetFieldSelections(Domain),'*WEB*') in the second line. That should take care of it, unless there are other values you need to exclude.

Re: if and 'and condition'

Thu, 19 Feb 2015 20:55:29 GMT

overall there are 3 conditions:

if(GetSelectedCount(Region)=0 AND GetFieldSelections(Domain)='MOPS',8.00,

if(GetSelectedCount(Region)=0 ANDWildMatch(GetFieldSelections(Domain),'*MOPS*','*Reports*') ,10.00,

if(GetSelectedCount(Region)=0 ANDWildMatch(GetFieldSelections(Domain),'*WEB*','*Reports*') ,13.00

if(GetSelectedCount(Region)=0 ANDWildMatch(GetFieldSelections(Domain),'*WEB*','*MOPS*') ,13.00

i know this is getting difficult. wish qlikview was as simple as excel. simply if(and would have worked. banging my head since the last 2 hours on this

Re: if and 'and condition'

Anonymous — Thu, 19 Feb 2015 21:05:27 GMT

OK - so you can use (concat(DISTINCT Domain, ',') like '*MOPS*' and concat(DISTINCT Domain, ',') like '*Reports*')

This would essentially do the same thing as GetFieldSelections... It will only bring back the selected fields in Domain. Try it out.

EDIT: I think that using concat will also put the values in alphabetical order so you can use like 'MOPS, Reports'

Re: if and 'and condition'

Thu, 19 Feb 2015 21:20:04 GMT

IT WORKED only with the second listbox ..but ii have to select one field from 1st lixbox also..that combination is giving a wrong output

=if(concat(DISTINCT Domain, ',') like '*MOPS*' and concat(DISTINCT Domain, ',') like '*Reports*' and GetFieldSelections(Region)='APAC' ,10,12)

Re: if and 'and condition'

Anonymous — Thu, 19 Feb 2015 21:23:47 GMT

You can use a combination of those functions to get exactly what you're looking for. Just make sure to use parenthesis to enclose certain logic if you get really deep into the weeds.