Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Order number | Product | Quantity |
1 | Ice cream | 2 |
1 | Bratwurst | 1 |
2 | Pancake | 2 |
2 | Potato | 2 |
3 |
Sauce |
1 |
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!
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
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;
best part is you can view other product2 that product1 has been paired with and vice-versa