Help wanted with filtering data

[#1]

Qlik Sense Server 2.2.1

New here and from a SQL Server background rather than Qlik, so please be gentle!

I have two tables in a SQL Server database: ArticleInfo (key ArticleCode and other attributes)

and ArticleList (key ArticleCode)

ArticleInfo is very big (200 columns and millions of records). What I want to do is load ArticleInfo nightly and then when I run a report, do a partial reload of ArticleList and only show the records from ArticleInfo where there are matching records in ArticleList without reloading the table from the database.

I can do the following

[ArticleList]:

LIB CONNECT TO [109.******];

REPLACE ONLY

LOAD [articleCode];

SQL SELECT "articleCode"

FROM "db"."dbo"."ArticleList";

[ArticleInfo]:

LIB CONNECT TO [109.******];

LOAD [articleCode]

      ,[weekOfYear]

      etc;

SQL SELECT [articleCode]

      ,[weekOfYear]

      etc

  FROM [db].[dbo].[ArticleInfo]

and using Thomas Friebel's utility https://community.qlik.com/docs/DOC-8866 can refresh ArticleList as I want without refreshing ArticleInfo. However, my reports are still based on the entire contents of ArticleInfo as the automatic data model is not INNER JOINing the tables

I can't see how to script the data so that I only report the items from ArticleInfo which appear in ArticleList and would be grateful for any pointers, especially if you can point me at a detailed example as I haven't had much joy interpreting the ones I've found on here so far.

[ogster1974]

‌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.

[#3]

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?

[ogster1974]

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

Andy

[#5]

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.

[#6]

Lightbulb time, with a lot of help from a Qlik consultant. Solution:

In the Data Load Editor create a section thus:

ADD ONLY

SelectedStock:

LOAD articleCode

  ,1 AS KPI;

SQL SELECT articleCode

FROM ArticleList;

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:

SUM({<KPI={1}>}StockOnHand)

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.

[ogster1974]

Glad u found a solution that worked for you.