Joins: one to Many.

[#1]

Good evening.

I am struggling to develop a model where I can see sell trough's in our retail stores.

My sales consist of multiple lines in the database, and my stock on hand equals one line per store per stock code.  I can join the two tables on Store and Stock code, but I am not sure ho to use this correctly in my qlikview model.

Sum(Sales) gives me the correct sales. sum(Stock on hand) gives me an inflated value.

Sell through = sum(sales for the week)/Stock on Hand.

Hopefully my question makes sense?

Regards.

[robert_mika]

Not seeing your data but  guess you need to group the Sales lines and then use join in your data model.

Post sample of your data or the app to give more correct solution.

[Oleg_Troyansky]

Hi Thomas,

yes, your question makes a perfect sense, and this is one of the most difficult issues in QlikView data modeling - how to load multiple fact tables with different granularity and how to keep different metrics in order, despite the different granularity.

First of all, you certainly don't want to JOIN the two tables, because of the problem you already mentioned - your Stock data will get multiplied by the number of Sales rows per Product and Store.

Basically you have three choices (I would personally support two of them):

1. Load your data "as is" and let it link "as is" without worrying too much about data modeling. In this case, you are likely to get one or more Synthetic keys and in some extreme cases, your data model may get linked incorrectly. While these models can sometimes work with small and simple datasets, I usually discourage developers from doing that.

2. Link your multiple fact tables using the data modelling technique called the "Link Table". This structure is slightly better than the previous one, because it explicitly replaces the Synthetic keys with your explicitly defined concatenated key fields, and if you do it right, you can avoid most of the data modelling pitfalls such as data duplication. The downside of these models is that they tend to be heavy on large datasets, for a variety of reasons that need to be described in a separate article. So, this option is only viable with a relatively small and simple data set.

3.  Concatenate your multiple fact tables into a "Single Fact" model. This is the best performing and the most robust analytical data model. The challenge here is to restore associations that get "lost" when you concatenate your facts (Sales and Inventory, for example) instead of linking them. For example, if you selected a Customer (or Salesperson, or any other attribute of Sales) and wanted to see Inventory that is associated with them, you could easily do it with a Link Table but it's less trivial with the Single Fact. There are techniques to overcome that, too. For larger data sets, this is the only viable option.

I recommend that you check out my new book QlikView Your Business. In the book, I describe in detail how to build a Link Table and how to build a Single Fact model, and how to overcome challenges related to both models. This is the most detailed explanation of these techniques in the existing QlikView literature. In addition, one of the three main parts of the book is focused on Inventory Analysis, which may help you with your particular application.

best,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView an...

[#4]

I am new to the forum so I am not 100% how to include the data.

Hopefully the below sample helps.

Table 1				Table 2
sales				Stock on hand
dt	invoice	sku	qty sold	sku	soh
2015/10/26	1235879	A	1	A	10
2015/10/27	1235880	A	6	B	10
2015/10/28	1235881	A	73	C	10
2015/10/29	1235882	B	6
2015/10/30	1235883	C	8
2015/10/31	1235884	A	5
2015/11/01	1235885	B	5

[#5]

Thank you for the detailed response.

I will play with option 3 for now and see if I get it right.