6 Replies Latest reply: Sep 25, 2017 11:40 PM by eduard Hörhager

# Which person has red and blue und NOT yellow or NOT black?

Hi all,

Tab1(color):

red

green

yellow

blue

black

Tab2(person):

Hans

Martin

Mark

Tab3(person,color):

Hans, red

Hans, blue

Martin, red

Martin, blue

Martin, yellow

Martin, blue

Mark, black

Now I need an answer for this Question:

which person has red and blue und NOT yellow or NOT black?

in the example form above the answer is: Hans, Mark

How can I solve this in Qlikview?
the user must be able to change the negative and positive values at any time.

THANK YOU FOR ANY HELP?

• ###### Re: Which person has red and blue und NOT yellow or NOT black?

Where do you need this? In a text box object? Also, why is Mark included, he has color black? May be try this:

Concat(DISTINCT {<person = (p({<color = {'red'}>})*p({<color = {'blue'}>}))-(p({<color = {'yellow'}>})+p({<color = {'black'}>}))>} person, ', ')

• ###### Re: Which person has red and blue und NOT yellow or NOT black?

If I change the data to this

Table:

person, color

Hans, red

Hans, blue

Martin, red

Martin, blue

Martin, brown

Martin, blue

Mark, black

];

I get Hans, Martin

• ###### Re: Which person has red and blue und NOT yellow or NOT black?

Hi Sunny,

I need a more flexible way.

Person:

person

Hans

Martin

Mark

];

Colors:

color

red

blue

brown

black

green

yellow

];

LIKES:

person, color

Hans, red

Hans, blue

Martin, red

Martin, blue

Martin, brown

Martin, blue

Mark, black

];

now i need two listboxes:

Color YES

and

Color NO

first example:

in Color YES I select blue and brown.

in Color NO I select red and yellow.

and in the tablebox (or any other List-object) "Dont Like" should appear:

Hans, Martin

second example:

in Color YES: I select black and blue.

in Color NO:  I select red.

and in the tablebox (or any other List-object) "Dont Like" should appear:

Mark

thanks for help!

• ###### Re: Which person has red and blue und NOT yellow or NOT black?

In your second example, why would Martin not show up?

• ###### Re: Which person has red and blue und NOT yellow or NOT black?

because Martin don't have black & blue (which are selected in the "Color YES")

and also Martin HAVE red.

LIKES:

person, color

Hans, red

Hans, blue

Martin, red

Martin, blue

Martin, brown

Martin, blue

Mark, black

];

• ###### Re: Which person has red and blue und NOT yellow or NOT black?

maybe I can show it better with this example:

Person:
Person
Hans
Martin
Mark

Michael
]
;

Products:
Product
A1

A2

A3

A4

A5

A6

A7

A8

];

Sales:
Person, Product
Hans, A1
Hans, A2
Martin, A1
Martin, A3
Martin, A5
Mark, A5
]
;

Question:

Who bought A1 and A2 but NOT A3

Martin

Who has NOT bought A2

Martin, Mark, Michael

Who bought A1 BUT NOT A2

Martin, Mark Michael

The selection "bought" and "NOT bought" should be possible in two list boxes.

thanks.