Counting occurances of products being ordered together

[Anonymous]

Hello!

I'm trying to count the occurrences of two products being ordered together in QlikSense Cloud. The purpose is seeing which products often are being bought together. My data table looks along the following:

Order number	Product	Quantity
1	Ice cream	2
1	Bratwurst	1
2	Pancake	2
2	Potato	2
3	Sauce	1

The desired result based on this would be to count the occurrences that two products has been ordered together, along with the names of the products. Something along these lines:

Product name	Product name	Occurances of being ordered togheter
Ice cream	Bratwurst	1
Pancake	Potato	1

I have tried to make a calculated field, but having trouble managing the calculation and getting two separate products the be compared. Ideally, if two products haven't had the same order number, they wouldn't be seen in the result table. I understand that I somehow need to match the products having the same order number, and count that as an occurrence.

Happy for any pointers on how to solve this!

[MayilVahanan]

Hi 

Try like below

T1:
Load * Inline
[
Ordernumber Product Quantity
1 Ice cream 2
1 Bratwurst 1
2 Pancake 2
2 Potato 2
3 Sauc 1

](delimiter is ' ');

Load Ordernumber, Concat(DISTINCT Product, ',') as ProductsPerOrder Resident T1 Group by Ordernumber;

Exp: Count({<Ordernumber={"=SubStringCount(ProductsPerOrder, ',')>0"}>}Distinct Ordernumber)

Hope it helps

[vinieme12]

raw:
LOAD
* Inline [
Ordernumber,Product,Quantity
1,Ice cream,2
1,Bratwurst,1
2,Pancake,2
2,Potato,2
3,Sauce,1
3,Bratwurst,1
4,Pancake,1
4,Ice cream,1
];

temp_Combinations:
Load Ordernumber, Product as Product1
Resident raw;

left join(temp_Combinations)
Load Ordernumber,Product as Product2
Resident raw;

NoConcatenate
Combinations:
Load * Resident temp_Combinations where Product1<>Product2;
Drop table temp_Combinations;

[vinieme12]

best part is you can view other product2 that product1 has been paired with and vice-versa