Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
rajuamet
New Contributor II

How to find Associated Products - Market Basket Analysis

Please see the attachment for details. Help me to list out the associated products in straight table when I select a product from a list box.

1 Solution

Accepted Solutions
sibideepak
Contributor II

Re: How to find Associated Products - Market Basket Analysis

Hi Raju ,

Try this expression as Dimension ,

=Aggr(Only({<ItemCode=e(ItemCode), InvoiceNo=p({<ItemCode=p(ItemCode)>}InvoiceNo)>}ItemCode),ItemCode)

View solution in original post

6 Replies
morganaaron
Valued Contributor

Re: How to find Associated Products - Market Basket Analysis

Hi Raju,

Attached my version here. I stripped out your expressions as I couldn't quite understand what purpose they were serving, and I replaced the ItemCode exclusion with an E()  - which does the opposite to your P() expression to get the same InvoiceNo.

You can extend this to rely on other field if need be, but check the expression of the Market Basket Analysis table to see what's going on.

As one extra point, and something that isn't well documented, calling an Alternate State "1" won't be recognised when trying to reference in the document. Because the "exclude all selections" state is represented by 1, although it allows you to create a state called 1 (whilst not letting you call one $!), it won't recognise it using the standard syntax - it will always use the exclude selections state "1". I replaced this with A1 as an example!

Aaron

rajuamet
New Contributor II

Re: How to find Associated Products - Market Basket Analysis

Hi Aaron Morgan, it served the purpose of listing the ItemCodes. But I need to add more expressions to the table. So, is it possible to give it as calculated dimension instead of giving it as an expression and hiding the column.

morganaaron
Valued Contributor

Re: How to find Associated Products - Market Basket Analysis

You can add more expressions but use the same P() and E() expressions wherever you use an aggregation. It isn't possible to do so in a calculated dimension in the format I've suggested, for that you'd need to pass values into your alternate state using an OnSelect field trigger or something similar.

sibideepak
Contributor II

Re: How to find Associated Products - Market Basket Analysis

Hi Raju ,

Try this expression as Dimension ,

=Aggr(Only({<ItemCode=e(ItemCode), InvoiceNo=p({<ItemCode=p(ItemCode)>}InvoiceNo)>}ItemCode),ItemCode)

View solution in original post

rajuamet
New Contributor II

Re: How to find Associated Products - Market Basket Analysis

Exactly what I wanted!!! Thanks Deepak.

arunqlik2view
Contributor

Re: How to find Associated Products - Market Basket Analysis

Hi ,

I'm also trying to use Market Basket Analysis. For some reason I'm not able to QVW file provided in this discussion, so it is hard for me to know as how your syntax is as dimension. I tried using it in the chart but I don't get the results . Please tell me in which chart section should I use this syntax

=Aggr(Only({<ItemCode=e(ItemCode), InvoiceNo=p({<ItemCode=p(ItemCode)>}InvoiceNo)>}ItemCode),ItemCode)


Thanks in advance,

Arun