Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
NewTable:
LOAD Customer
Where Index(List, 'A') > 0 and Index(List, 'B') > 0 and Index(List, 'C') > 0;
LOAD Customer,
Concat(DISTINCT Products, '|') as List
Resident .....
Group By Customer;
Thanks, I'll give it a try.
Do I need to say Noconcatenate prior to "NewTable:"?
Not unless you are bringing all your dimensions from your resident load, but having it there does no harm
Another option could be to load the customer with 3 distinct product (A B C)
load customer where cnt = 3;
load
customer, count(DISTINCT product) as cnt
Resident S
where mixmatch(product, 'A', 'B', 'C')
Group by customer;
Hi,
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:
or
Using a straight table expression to find customers having purchased all selected products:
tabSales:
LOAD RecNo() as ID,
Ceil(Rand()*20) as CustomerID,
Chr(64+Ceil(Rand()*7)) as Product
AutoGenerate 100;
tabCustProd:
LOAD Distinct
CustomerID,
Product as ProductForAndMode
Resident tabSales;
maybe this helps
regards
Marco
Thanks everybody. These are exactly what I needed. I used the maxgro solution for my current need.