topic Selection problem in QlikView

Selection problem

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have data similar to the following simplified table

Product	Vendor	Status
1	A	old
1	B	active
1	C	old
2	A	old
2	B	old
2	C	old
3	A	active
3	B	old
3	E	old
4	A	old
4	B	old
4	D	old
4	E	old

I need a list of all Products which have no record with status "active". In the example the result should show product 2 and product 4.

I have not figured out how to accomplish this. If I select Status='active', the other 2 products show up but I cannot reverse the selection result. If I select Status='old' all products show up.

Probably there's a trick to get the desired result?

Thanks

Jens

Re: Selection problem

swuehl — Thu, 25 May 2017 21:52:14 GMT

Yes, you should be able to select excluded values from the context menu of field Product after selecting 'active'.

Or using set analysis:

=Concat(DISTINCT {<Product = e({<Status = {'active'}>}) >} Product,', ')

Re: Selection problem

sibusiso90 — Thu, 25 May 2017 22:02:43 GMT

Hi Jens

I have compiled a set analysis script that excludes products that have active with it. I am not sure if you want to use it only when you are filtering it. If so I will look for a solution as at the moment I don't have anything.

Re: Selection problem

Fri, 26 May 2017 22:35:33 GMT

Thanks. As I cannot open your script in QV personal edition, could you post it as text? Thanks

Re: Selection problem

sibusiso90 — Mon, 29 May 2017 08:19:51 GMT

HI Jens

you can use the expression below, if you have QlikSense you can find the below app which will not require licensing

Aggr(only({<Product=e({<Status={'active'}>}Product)>}Product),Product)

I hope it helps.