topic Re: Market Basket-find associate items not on same orders but on same session in QlikView

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

Tue, 28 Mar 2017 13:48:42 GMT

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 ID	InvoiceID	ItemID	Category
3620	1	211	DL
3620	1	212	DL
3620	1	213	DB
3620	1	214	DB
3620	1	215	W
3620		216	Q
3620		217	Q
3620		218	E
1111	2	219	DL
1111	2	220	E
1111	2	221	JL
1111		222	JL
1111		223	Q
1111		224	W

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.

Category	No. of session
Q	2
E	1
W	1

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

sunny_talwar — Tue, 28 Mar 2017 15:40:52 GMT

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

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

Wed, 29 Mar 2017 01:33:14 GMT

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.

Category	No. of session
Q	2 (been in both 3620 and 1111)
E	1(been in both 3620 and 1111)
W	1(been in 1111)
JL	1(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?

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

sunny_talwar — Wed, 29 Mar 2017 01:37:39 GMT

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

When DL is selected in Category

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

Isn't this what you wanted?

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

Wed, 29 Mar 2017 02:03:57 GMT

Yes, Sunny,

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

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

sunny_talwar — Wed, 29 Mar 2017 02:09:32 GMT

‌I have attached the qvw for you here

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

Wed, 29 Mar 2017 02:40:37 GMT

Thanks!!! problem solved!

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

Wed, 29 Mar 2017 06:58:52 GMT

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 :

Category	No. of session
JL	1
Q	1
W	1

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

mahendragaur — Wed, 29 Mar 2017 07:35:45 GMT

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?

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

sunny_talwar — Wed, 29 Mar 2017 08:02:32 GMT

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?

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

Wed, 29 Mar 2017 08:24:09 GMT

Sunny,

Because i want to know for each category, how many times it have not been sold with the selected category on the same session..in the sample data above, i assume that i would click category E( have been sold on the session) on a list box , and the chart shows that JL have have not been sold with category E in one session( 1111)....

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

Wed, 29 Mar 2017 08:26:25 GMT

i am able to find out the category that have been sold together with the selected category with the below expression with the use of invoice ID, however i cannot find those not sold together with the selected item......

=count(DISTINCT {$<InvoiceID=P({$}InvoiceID),Category=E({$}Category),Flag = {0}>}InvoiceID)

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

Wed, 29 Mar 2017 08:27:14 GMT

Tried many times, could you kindly help.

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

sunny_talwar — Wed, 29 Mar 2017 08:30:11 GMT

Not 100% sure, but try this

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

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

Wed, 29 Mar 2017 08:46:44 GMT

Thanks so much sunny, this is what i want to acheive.

you saved my day.

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

sunny_talwar — Wed, 29 Mar 2017 09:22:08 GMT

Super... I am glad you got what you wanted...

Best,

Sunny