Hi scott. First things first you don't need to worry about joins, inner, outer Etc.. Qliks associate data model handles all that automatically. All you have to do is name the key field in the two tables the same and they will automatically be joined.
IN the dashboard dependent on the chart type you are using you can use the check to hide if null ifappropriate to exclude dimensions with null measure values.
Thanks for responding Andy. My situation is that I have table ArticleInfo (fields ArticleCode, WeekOfYear, StockOnHand) table with 14M records and an ArticleList table with the 10 records that I am interested in. They have field ArticleCode in common and the data model shows the join as expected. If I create a basic table report with dimension WeekOfYear and measure Sum(StockOnHand) I see the data for all 14M records in ArticleInfo. What I want to do is restrict all reports so that they only show data relating to the to records in ArticleList and then when I do a partial reload of ArticleList with a different subset of articles the same report will show data relating to the new subset. What is the best approach to doing this?
There are lots of ways to do this. Your an SQL Expert so you could filter out the unnecessary ArticleInfo data in a view inner joining on ArticleCode and import that into your Qlik Model. This would have the benefit of reducing your apps footprint making the reload and use of the dashboard quicker for the users.
If you need all the data in your dashboard then dependent on the visualisation you are looking to use you can use Set Analysis in your measures, dimension to filter your data as you use it. I've attached a document I've found useful myself when getting started with it.
Hope this helps
Les set analysis_ENG.pdf 212.3 K
Thanks Andy. I've read through the document a few times and the lightbulb over my head has so far failed to light. I'm using a line chart, if that makes any difference. I don't think going down the view route is going to work as the whole idea is to prevent the user having to reload the 14M row table every time they open the report.
Lightbulb time, with a lot of help from a Qlik consultant. Solution:
In the Data Load Editor create a section thus:
,1 AS KPI;
SQL SELECT articleCode
So when I do a partial reload, only this table is reloaded and it contains a dummy field which always has the value 1. I can then define Master item measures on fields in ArticleInfo such as StockOnHand like this:
which gives me the total stock on hand for all articles where the dummy field (KPI)=1, i.e. where the article exists in ArticleList. The Set Analysis document that Andy supplied did not at first make sense as I couldn't see how to apply it but the addition of the dummy KPI field makes all the difference (for me at least). Thanks again to ogster1974 for taking the time to try and point me in the right direction.