topic If value AND value OR value, then... in QlikView

If value AND value OR value, then...

Thu, 16 May 2013 12:32:42 GMT

Hey guys,

Question concerning conditional formatting in Qlikview.

3 Dimension:

Type_one= a, b, c

Type_two= x, xx, xxx, y, yy, z, zz, zzz

Order: those are order numbers: eg. 123, 111, 222, 333, 444, ...

I am using the formatting option on the dimension of "order" (background color)

I want to make the background of "order" red when:

=if((Type_one='a' AND Type_two='x*' , rgb(231,0,24))

Using the * does not work for some reason.

So I tried something else and I listed all the values:

=if((Type_one='a' AND Type_two='x' OR 'xx' OR 'xxx' , rgb(231,0,24))

It does work with this formula, but it only turn the background of "x" red, it ignores everything else that comes after the "OR's"

How can I fix this?

Thank you!

Re: If value AND value OR value, then...

er_mohit — Thu, 16 May 2013 12:43:27 GMT

Try this

=if(Type_one='a' AND Type_two='x' OR Type_two='xx' OR Type_two='xxx' , rgb(231,0,24))

=if(Type_one='a' AND wildmatch(Type_two,'x*') , rgb(231,0,24))

Re: If value AND value OR value, then...

kumarnatarajan — Thu, 16 May 2013 12:45:15 GMT

Hi,

Try this.

if(Only({1}Type_one)='a' AND wildmatch(Only({1}Type_two),'x*') , rgb(231,0,24))

Re: If value AND value OR value, then...

Thu, 16 May 2013 12:48:48 GMT

=if(Type_one='a' AND Type_two=pick(wildmatch(Type_two,'x*'),'x*') , rgb(231,0,24))

Re: If value AND value OR value, then...

Thu, 16 May 2013 13:24:38 GMT

Thanks to all!

What a quick service!

Small remark though,

How can I turn everything red that does NOT contain 'x*'

Using this one:

=if(Type_one='a' AND wildmatch(Type_two,'x*') , rgb(231,0,24))

Thx!

Re: If value AND value OR value, then...

er_mohit — Thu, 16 May 2013 13:32:18 GMT

if(Type_one='a' AND wildmatch(Type_two,'x*') , rgb(231,0,24),lightred())

hope it helps

Re: If value AND value OR value, then...

Thu, 16 May 2013 13:54:46 GMT

No that does not work, you are using two different types of red now.

But I found a solution, white as the first result and red as the second.

er.mohit wrote:
if(Type_one='a' AND wildmatch(Type_two,'x*') , white, red)
hope it helps

But that is not really what I was looking for, I was hoping to use something like '<> x*' in the formula.

Do you have any idea?

_________

And how do I get the following two parts into one function:

if(Type_one='a' AND wildmatch(Type_two,'x*') , white, red)

if(Type_one='b' AND wildmatch(Type_two,'x*') , red, white)

In the first one it is good to have x in the values. So everything without x needs to be red.

In the other one it is bad to have x in the values. So everything with x needs to be red.

Re: If value AND value OR value, then...

er_mohit — Thu, 16 May 2013 17:53:01 GMT

if(Type_one='a' AND Type_two<>'x*' rgb(231,0,24))

if(Type_one='b' AND Type_two='x*' rgb(231,0,24))

hope it helps you

Re: If value AND value OR value, then...

Fri, 17 May 2013 06:28:21 GMT

Hey er.mohit,

No that one does not work unfortunately.. Looks like from the second you try to use a * in the formula, it needs a (wildmatch).

Because your formula with "<>" does work, bot only if I look for 'x', not for 'x*'.