Sum dimension values that are found across multiple fields

[#1]

Hi Qlik Community!

I have a doozy for you. I converted the subject matter in the example to fruit.

A user needs to have the ability to select a fruit that is purchased at a grocery store. Based on that selected fruit, we need to show them what fruit combinations are most popular when that fruit is purchased. For example, if the user wants to know what fruits are bought most frequently with oranges then we need to return the combos that are most popular (i.e. cherries, apples, bananas are most commonly purchased with oranges). This is not a problem to get at.

The problem I'm having is in the next question that we have to answer for the user.

Of all combination fruits purchased with, say, oranges, which fruits are most commonly purchased. We are separating out the combination fruits into different fields. How do you look at all the fields to find the most common fruits. Please see my attached example.

I would sooo appreciate any assistance you can provide. I'm at a roadblock with this.

Thanks so much.

[stigchel]

Without the xls it is difficult to provide you with a working example, but I would advise you to look at the crosstable load to bring together the different Fruit fields, something like

Data:

LOAD [Fruit Combination],

     [Fruit 1],

     [Fruit 2],

     [Fruit 3],

     [Fruit 4],

     [Fruit 5],

     TotalFruit

FROM

(ooxml, embedded labels, table is Sample);

CrossTable (Fruit,Data) load *

Resident Data;

[ramoncova06]

there is one issue with your example

normally in store ticket all your items are broken down by product level, that way is easier to count them separately, which is something that we are unable to do with your data set

to me it seems as you already did the hard part which is combining the products together, maybe if you provided more granular data it might be easier to get this done

[#4]

Ramon,

That is very perceptive. Here is the big picture...The real data that this example represents goes through an iteration in a different model then runs through the current model...both of which are done in excel. I have been asked to take the second model and attempt to recreate that model in QlikView. We have asked that we start from the beginning and pull the data directly from the data source but have had push back. What we can do is change the format of the data we bring into QlikView. It's painful not starting from scratch.