I have a table that contains sales of products A,B,C,D,E,F & G.
How would I script to create a table of distinct customers that have purchased products A,B & C?
In order to qualify, the customer needs to have purchased all three of the products.
May be like this:
Where Index(List, 'A') > 0 and Index(List, 'B') > 0 and Index(List, 'C') > 0;
Concat(DISTINCT Products, '|') as List
Group By Customer;
Another option could be to load the customer with 3 distinct product (A B C)
load customer where cnt = 3;
customer, count(DISTINCT product) as cnt
where mixmatch(product, 'A', 'B', 'C')
Group by customer;
although you are looking for a script solution, here are some front end solutions as well:
Using AND-mode in List Boxes:
Using a calculated filed in a List Box to select product combinations per customer:
Using a straight table expression to find customers having purchased all selected products:
LOAD RecNo() as ID,
Ceil(Rand()*20) as CustomerID,
Chr(64+Ceil(Rand()*7)) as Product
Product as ProductForAndMode
maybe this helps