Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

maxsheva
Contributor

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
serj_shu
Valued Contributor

Re: Find products which were sold together

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). !

1 Reply
serj_shu
Valued Contributor

Re: Find products which were sold together

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). !