topic Re: Selecting IDs restricted to certain parameters in QlikView

Selecting IDs restricted to certain parameters

Thu, 28 Jan 2016 21:18:03 GMT

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

Re: Selecting IDs restricted to certain parameters

swuehl — Thu, 28 Jan 2016 21:39:01 GMT

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

Re: Selecting IDs restricted to certain parameters

sunny_talwar — Thu, 28 Jan 2016 21:46:37 GMT

Try this:

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

Re: Selecting IDs restricted to certain parameters

swuehl — Thu, 28 Jan 2016 22:01:18 GMT

Sunny,

what about an additional record

5, D

Re: Selecting IDs restricted to certain parameters

sunny_talwar — Thu, 28 Jan 2016 22:04:13 GMT

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)

Re: Selecting IDs restricted to certain parameters

Thu, 28 Jan 2016 22:19:10 GMT

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

Re: Selecting IDs restricted to certain parameters

sfatoux72 — Thu, 28 Jan 2016 22:26:39 GMT

Try with that :

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

Re: Selecting IDs restricted to certain parameters

swuehl — Thu, 28 Jan 2016 22:41:25 GMT

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

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

Re: Selecting IDs restricted to certain parameters

Thu, 28 Jan 2016 23:08:35 GMT

Works. Thanks!

Re: Selecting IDs restricted to certain parameters

sunny_talwar — Fri, 29 Jan 2016 00:43:03 GMT

What about this Stefan?

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

Re: Selecting IDs restricted to certain parameters

swuehl — Fri, 29 Jan 2016 10:30:39 GMT

Sunny, this looks good, should also work fine.

Re: Selecting IDs restricted to certain parameters

sunny_talwar — Fri, 29 Jan 2016 10:33:58 GMT

I am just curious how do you do all of this in your head? I mean I see you come up with solutions to thing which I might not even figure even if I had sample for. You are truly a genius and my QlikView role model