How to exclude a value in a set analysis

[bernd_schuetze]

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

[ramoncova06 · Accepted solution]

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

[rubenmarin]

Hi Bernd, you can use something like this:

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

[sunny_talwar]

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

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

[Anonymous]

Hi, Maybe.

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

[bernd_schuetze]

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!

[sunny_talwar]

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

[bernd_schuetze]

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

[bernd_schuetze]

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

[sunny_talwar]

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

Best,

Sunny