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: 
Not applicable

Market Basket-find associate items not on same orders but on same session

Help!!

I have the sample data like below, I want to do a basket analysis, e.g. when I select an category on an list box(e.g. DL) the graph would show the associate category that is in the same session id, but without invoice id (in terms of no. of time of session)

Session IDInvoiceIDItemIDCategory
36201211

DL

36201212DL
36201213DB
36201214DB
36201215W
3620216Q
3620217Q
3620218E
11112219DL
11112220E
11112221JL
1111222JL
1111223Q
1111224W

for example, when I select DL on a list box,The result should be below, how can I achieve below  with what dimension and expression??? Many Thanks for help.

CategoryNo. of session
Q2
E1
W

1

1 Solution

Accepted Solutions
15 Replies
sunny_talwar

Why is JL not included? DL is in Session ID 1111 also, so it should be included in the chart? If it does, try this

Not applicable
Author

Hi, Sunny,

Thanks for your reply,

Yes, JL should be included, the result should be below when I choose DL, however, the attached qvw only show me all the rows with DL be the category.

CategoryNo. of session
Q2 (been in both 3620 and 1111)
E1(been in both 3620 and 1111)
W

1(been in 1111)

JL1(been in 1111)

what i want is , e.g.  in session 3620, when i choose DL, it gives me Q and E, which are in the same sessionid, but without invoice id..

can you advice how to do that?

sunny_talwar

From what you mentioned, isn't this what you wanted?

When DL is selected in Category

Capture.PNG

When DL is selected in Category and Session ID 3620 is selected

Capture.PNG

Isn't this what you wanted?

Not applicable
Author

Yes, Sunny,

that is the result i want! can you tell me how to do the expression?

Not applicable
Author

Thanks!!! problem solved!

Not applicable
Author

hi Sunny,  i have another problem, how can i achieve, e.g, when i select an category with invoice id( e.g., E), the graphs count the session id of each category that have no invoice id? with the above sample data, the result should be :

CategoryNo. of session
JL1
Q1
W

1

mahendragaur
Creator
Creator

Hello Sunny,

=Count(DISTINCT{<Category = p({<Category, [Session ID] = p([Session ID]), Flag = {1}>}), Flag = {1}>}[Session ID])

Could you please explain how it works and what is the use of Flag here?

sunny_talwar

I am sorry, but I am confused about why is E not included in the desired output? Session ID 3620 have E without any InvoiceID, shouldn't that be included also?

Capture.PNG