If value AND value OR value, then...

[#1]

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!

[er_mohit]

Try this

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

or

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

[kumarnatarajan]

Hi,

Try this.

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

[#4]

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

[#5]

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!

[er_mohit]

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

hope it helps

[#7]

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.

[er_mohit]

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

[#9]

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*'.