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: 
maxsheva
Creator II
Creator II

Find products which were sold together

Hi,

From the data below I have to find products which were sold together in different ID's.

For example, the combination of Product1 and Product3 were sold 3 times where Product 1 sold 3 pieces as well as Product3 has been sold 7 pieces.

So I need result like this

ProductsTogether    | Sold times together | Product  1st   |   Sold pieces | Product 2nd  | Sold pieces |

Product1&Product3 | 3 times                       | Product1        | 3 pieces          | Product3       | 7 pieces       |

Product1&Product2 | 1 time                         | Product1        | 1 piece             | Product2       | 5 pieces       |

-- etc --

Think such task should be done before. Please share your experience with me.

Thank you in advance!

Table:

LOAD * Inline

[ID, Product, Sold quantity

001, Product1, 1

001, Product2, 5

001, Product3, 1

002, Product1, 1

002, Product3, 2

003, Product1, 1

003, Product3, 4

003, Product5, 2

]

1 Solution

Accepted Solutions
Sergey_Shuklin
Specialist
Specialist

Hello, Max!

For solving such tasks the best way is using a matrix, especially if you want to cross all with all.

The script will be:

temp:

LOAD * Inline

[ID, Product, Sold quantity

001, Product1, 1

001, Product2, 5

001, Product3, 1

002, Product1, 1

002, Product3, 2

003, Product1, 1

003, Product3, 4

003, Product5, 2

];

Join(temp)

LOAD ID, Product as Product2,[Sold quantity] as [Sold quantity2] Resident temp;

After that you can create a matrix:

sales_matrix.png

And of course you can change an expression with output string by your taste.

P.S.: If you find my solution helpful or correct, please, close the thread (Qlik Community Tip: Marking Replies as Correct or Helpful). !

View solution in original post

1 Reply
Sergey_Shuklin
Specialist
Specialist

Hello, Max!

For solving such tasks the best way is using a matrix, especially if you want to cross all with all.

The script will be:

temp:

LOAD * Inline

[ID, Product, Sold quantity

001, Product1, 1

001, Product2, 5

001, Product3, 1

002, Product1, 1

002, Product3, 2

003, Product1, 1

003, Product3, 4

003, Product5, 2

];

Join(temp)

LOAD ID, Product as Product2,[Sold quantity] as [Sold quantity2] Resident temp;

After that you can create a matrix:

sales_matrix.png

And of course you can change an expression with output string by your taste.

P.S.: If you find my solution helpful or correct, please, close the thread (Qlik Community Tip: Marking Replies as Correct or Helpful). !