Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
gmuradov
Partner - Contributor III
Partner - Contributor III

Filtering on a chart which shows most items are frequently bought together

Hello, 

I am trying to make a barchart to show which 2, 3 or 4 items are most frequently bought together. 

My sales orders are stored in the table ps2_trans, the sales orders rows in ps2_rows where I have item_id and trans_id (id of the sales order).

This is my load script 
Select
t.id AS trans_id,
r.id AS row_id,
GROUP_CONCAT(r.item_id ORDER BY r.item_id SEPARATOR ' == ') AS concat_item_ids,
GROUP_CONCAT(i.nm->>"$.bg" ORDER BY r.item_id SEPARATOR '; ') AS concat_items,
COUNT(r.id) AS rows_cnt,
r.item_id as item_id
FROM
ps2_trans t,
ps2_rows r,
ps2_items i
WHERE
t.id=r.trans_id AND
r.item_id=i.id
GROUP BY
t.id
HAVING
rows_cnt > 1;

this is my dimension: =IF(LEN(concat_items) - LEN(REPLACE(concat_items, ';', '')) = 1(2 or 3), concat_items)
this is the measure: COUNT(trans_id)

since i am using group by t.id in my query therefore i cannot apply any filters on my bar chart (brand, category, item) 
can anybody help with a more intelligent way to solve this problem?

Labels (1)
12 Replies
marcus_sommer

It will depend on the variance of working logic in dependency to further requirements which way would be most suitable in regard to the size of the data-set. I assume that incremental approaches are possible and then the overall size has not so much impact.

Qrishna
Master
Master

So whats the expected output from this dataa?

gmuradov
Partner - Contributor III
Partner - Contributor III
Author

the expected output is a barchart where the dimensions are the item combinations and the measure how many times are bought together