topic Re: How to find Associated Products - Market Basket Analysis in QlikView

How to find Associated Products - Market Basket Analysis

Anonymous — Tue, 07 Apr 2015 05:20:57 GMT

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.

Re: How to find Associated Products - Market Basket Analysis

morganaaron — Tue, 07 Apr 2015 09:36:55 GMT

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

Re: How to find Associated Products - Market Basket Analysis

Anonymous — Tue, 07 Apr 2015 10:53:11 GMT

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.

Re: How to find Associated Products - Market Basket Analysis

morganaaron — Tue, 07 Apr 2015 11:06:03 GMT

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.

Re: How to find Associated Products - Market Basket Analysis

sibideepak — Tue, 07 Apr 2015 11:56:09 GMT

Hi Raju ,

Try this expression as Dimension ,

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

Re: How to find Associated Products - Market Basket Analysis

Anonymous — Tue, 07 Apr 2015 12:25:59 GMT

Exactly what I wanted!!! Thanks Deepak.

Re: How to find Associated Products - Market Basket Analysis

arunqlik2view — Tue, 12 Jun 2018 01:27:41 GMT

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