Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Bernd, you can use something like this:
=Concat({<Customer=E({<Product={'Banana'}>} Customer)>} Customer, ', ')
There may be an easier solution, but this also works:
=Concat(DISTINCT{<Customer = p({<Product = {'Apple'}>})>-<Customer = p({<Product = {'Banana'}>})>}Customer)
Hi, Maybe.
only({<Product -={'Banana'}>}Product)
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!
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
Yes and no, the dirct concat of the customers is getting too big, but the Variable with the ProductList I use of course
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
Regardless, you have found your solution . I am glad someone was able to resolve the issue
Best,
Sunny