Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to script to find custs with purchases of 3 specific prods

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.

6 Replies
sunny_talwar

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;

Not applicable
Author

Thanks, I'll give it a try. 

Do I need to say Noconcatenate prior to "NewTable:"?

sunny_talwar

Not unless you are bringing all your dimensions from your resident load, but having it there does no harm

maxgro
MVP
MVP

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;

MarcoWedel

Hi,

although you are looking for a script solution, here are some front end solutions as well:

Using AND-mode in List Boxes:

QlikCommunity_Thread_239559_Pic1.JPG

QlikCommunity_Thread_239559_Pic5.JPG

Using a calculated filed in a List Box to select product combinations per customer:

QlikCommunity_Thread_239559_Pic2.JPG

QlikCommunity_Thread_239559_Pic3.JPG

QlikCommunity_Thread_239559_Pic4.JPG

QlikCommunity_Thread_239559_Pic6.JPG

or

Using a straight table expression to find customers having purchased all selected products:

QlikCommunity_Thread_239559_Pic7.JPG

QlikCommunity_Thread_239559_Pic8.JPG

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

Not applicable
Author

Thanks everybody.  These are exactly what I needed.  I used the maxgro solution for my current need.