Selecting IDs restricted to certain parameters

[#1]

Hello,

I have a table something like this

ID          Parameter

1               A

1               B

1               C

2               A

2               B

3               A

3               C

4               B

4               C

4               D

Now I want to write a script that only gives me IDs that have either A or B and C so I want only ID 1 and 3 returned and not 2 and 4. I tried set analysis with something like

{<Parameter={'A','B'},Parameter={'C'}>} but I am also getting back 4 which I don't want because it also has D.

Can someone help me on this?

thanks,

Manoj

[swuehl]

You want to select IDs:

{<ID = p({<Parameter={'A','B'}>})*p({<Parameter={'C'}>}) >}

and if you only want to have Parameter A,B,C allowed:

{<ID = p({<Parameter={'A','B'}>})*p({<Parameter={'C'}>}) * e({<Parameter -= {'A','B','C'}>}) >}

edit: changed set analysis

[sunny_talwar]

Try this:

=Concat(DISTINCT {<ID = p({<Parameter = {A, B}>})*p({<Parameter = {C}>})/p({<Parameter -= {A,B,C}>})>}ID, ', ')

[swuehl]

Sunny,

what about an additional record

5,     D

?

[sunny_talwar]

My guess is that since you asked, it is not going to work . But being me, I am going to still test it out (not doubting your QlikView Skills, but for my learning)

[#6]

Hi swuehl,

Not sure if I am doing something wrong but I am still getting ID 4. Let me explain what I am doing

ID          Parameter     Value

1               A               2

1               B               3    

1               C               1

2               A               5   

2               B               6

3               A               2

3               C               3

4               B               4

4               C               2

4               D               1

I have selected a table chart type and have given ID as the dimension. My expression is

SUM{<ID = p({<Parameter={'A','B'}>})*p({<Parameter={'C'}>}), Parameter = {'A','B','C'} >} Value)

My table still shows a record for ID 4. Actually I don't care about the sum of the values and I am only using it to be able to use the set equation. I just need a way to get a list of IDs 1 and 3.

thanks for you help.

Manoj

[sfatoux72]

Try with that :

=Concat(DISTINCT {<ID = p({<Parameter = {'A', 'B'}>}) * p({<Parameter = {'C'}>}) - p({<Parameter =-{'A','B','C'}>})>}ID, ', ')

[swuehl · Accepted solution]

You probably missed that I've updated my suggested set expression:

{<ID = p({<Parameter={'A','B'}>})*p({<Parameter={'C'}>}) * e({<Parameter -= {'A','B','C'}>}) >}

[#9]

Works. Thanks!

[sunny_talwar]

What about this Stefan?

=Concat(DISTINCT {<ID = p({<Parameter = {A, B}>})*p({<Parameter = {C}>})-p({<Parameter -= {A,B,C}>})>}ID, ', ')