Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Gerhard
Contributor II
Contributor II

Return count of records in two categories by another dimension

Hi,

I have this data. using set analysis I want to count how many customers purchased product A in Order_Number 1 AND 2.

load * Inline [
Customer,Order_Number,Product
101,1,A
101,1,B
102,1,B
103,1,B
101,2,A
102,2,A
104,1,A];

I then want to display it in a table by product dimension.

Product|Count of customers

A              | 1

B              |0

I tried using intersection for the AND piece, but the problem this returns how many customers ordered in Order_Number 1 and 2. Irrelevant of the product.

Count(distinct{<Product={"=Count(distinct{<Order_Number={'1'}>}Order_Number)"}>*<Product={"=Count(distinct{<Order_Number={'2'}>} Order_Number)"}>} Customer)

Any help would be appreciated.

Thanks

 

Gerhard

Labels (1)
1 Solution

Accepted Solutions
Gerhard
Contributor II
Contributor II
Author

Thanks for the reply, unfortunately the suggestion does not make a difference.

We do now have a solution as below, not set analysis but rather in the script.

Table1:
load * Inline [
Customer,Order_Number,Product
101,1,A
101,1,B
102,1,B
103,1,B
103,2,B
101,2,A
102,2,A
104,1,A
104,2,A]
;


Customers:
LOAD Count(Customer&Product) As CustomerCount,
Product,
Customer
Resident Table1
Group By Product,Customer;
Drop Table Table1;
Exit Script

Gerhard_0-1694182719613.png

 

 

View solution in original post

2 Replies
agigliotti
Partner - Champion
Partner - Champion

Hi @Gerhard ,

Maybe this:

=Count( {< Customer = {"=count( {< Order_Number = {'1'} >} distinct Order_Number)>0"} * {"=count( {< Order_Number = {'2'} >} distinct Order_Number)>0"} >} distinct Customer )

I hope it can helps.

Best Regards

Gerhard
Contributor II
Contributor II
Author

Thanks for the reply, unfortunately the suggestion does not make a difference.

We do now have a solution as below, not set analysis but rather in the script.

Table1:
load * Inline [
Customer,Order_Number,Product
101,1,A
101,1,B
102,1,B
103,1,B
103,2,B
101,2,A
102,2,A
104,1,A
104,2,A]
;


Customers:
LOAD Count(Customer&Product) As CustomerCount,
Product,
Customer
Resident Table1
Group By Product,Customer;
Drop Table Table1;
Exit Script

Gerhard_0-1694182719613.png