topic Re: How to exclude a value in a set analysis in QlikView

How to exclude a value in a set analysis

bernd_schuetze — Tue, 28 Apr 2015 08:02:42 GMT

Hi,

I have a question for a set analysis.

Lets assume I want all customers who have Product A, but not Product B.

The (very simplified) Table is:

Customer	Product
Joe	Banana
Bud	Banana
Joe	Apple
Frank	Apple

So I would like to get all customers who never bought a banana. The expected Answer is "Frank".

In SQL I would do a subselect:

Select Customer from table where customer not in

(select customer where product = Banana)

Thanks,

Bernd

Re: How to exclude a value in a set analysis

ramoncova06 — Tue, 28 Apr 2015 15:15:41 GMT

this would do it

only({<Product -={'Banana'}>}Customer) -- this will only exclude the lines that have a banana product

=only({<Customer=E({<Product={'Banana'}>} Customer)>} Customer) -- this will exclude all the customers that bought a banana

*edited comment since I misread the question

Re: How to exclude a value in a set analysis

rubenmarin — Tue, 28 Apr 2015 15:19:41 GMT

Hi Bernd, you can use something like this:

=Concat({<Customer=E({<Product={'Banana'}>} Customer)>} Customer, ', ')

Re: How to exclude a value in a set analysis

sunny_talwar — Tue, 28 Apr 2015 15:24:58 GMT

There may be an easier solution, but this also works:

=Concat(DISTINCT{<Customer = p({<Product = {'Apple'}>})>-<Customer = p({<Product = {'Banana'}>})>}Customer)

Re: How to exclude a value in a set analysis

Anonymous — Tue, 28 Apr 2015 15:33:24 GMT

Hi, Maybe.

only({<Product -={'Banana'}>}Product)

Re: How to exclude a value in a set analysis

bernd_schuetze — Tue, 28 Apr 2015 16:13:14 GMT

Thanks - I tried this (similar thing) today as well, but unfortunately the script doesn't work correctly as the customer list to exclude is depending on the products selected on average 500.000 . The performance is also a disaster.

But thanks!

Re: How to exclude a value in a set analysis

sunny_talwar — Tue, 28 Apr 2015 16:16:28 GMT

Well I wouldn't hardcode the product, instead I would use a variable which would create a concatenated list of what needs to be included and what needs to be excluded. This will make the process little faster.

Performance wise I am not really sure as I don't have the data to test

Best,

Sunny

Re: How to exclude a value in a set analysis

bernd_schuetze — Tue, 28 Apr 2015 16:39:13 GMT

Yes and no, the dirct concat of the customers is getting too big, but the Variable with the ProductList I use of course

Re: How to exclude a value in a set analysis

bernd_schuetze — Tue, 28 Apr 2015 16:40:08 GMT

Thanks! That was it.

I modified it to fit to my real case, though:

=count({<AsOfCurrMonth={1}, RegUserID=E({<ProductFamily={$(ProductFamily)}>} RegUserID)>} distinct RegUserID)

Thanks,

Bernd

Re: How to exclude a value in a set analysis

sunny_talwar — Tue, 28 Apr 2015 16:41:22 GMT

Regardless, you have found your solution . I am glad someone was able to resolve the issue

Best,

Sunny