topic Re: QlikSense - Multiple Tables, Multiple Shared Fields in App Development

QlikSense - Multiple Tables, Multiple Shared Fields

Tue, 08 Aug 2017 14:31:54 GMT

Hello

I have a set of data (Excel attached) that share some common fields. In this example there are just two but in reality there are more.

In the example, we have sales by date and location, I also have a forecast (budget) for these locations to hit as a sales target for each date and location. So I created the common calendar fine. When I added the location I got the synthetic key added which according to all the posts I've read bar one, that's a bad thing.

What I'd like to know is what is the approach for this. I thought it was to create a common table with a data type the same way I had done with the common calendar but it still created a synthetic key as I formed a circle of data which appears to be incorrect. Do I have to put all the shared fields into the same shared table?

Here's what I've looked at so far:

Common calendar: http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

Synthetic Keys: https://community.qlik.com/thread/10279?start=30&tstart=0&_ga=2.85565337.1277186251.1502115616-1009540365.1501833945

Thank you in advance.

Re: QlikSense - Multiple Tables, Multiple Shared Fields

Anonymous — Tue, 08 Aug 2017 15:44:13 GMT

Often it is good to aim for a Star Schema in your Qlik Data Model.

If you do a web search for Qlik Star Schema you'll get a fair few hits like this one :

https://qlikcentral.com/2014/08/19/the-optimal-qlikview-data-structure/

It does not matter if it refers to QlikView, as opposed to Sense, as Qlik data modelling / scripting is the same View vs. Sense.

... and a good Qlik Star Schema will not have synthetic keys.

[Although for some scenarios synthetic keys can indeed be ok, if not actually good - But only using them if you make a conscious decide to do so e.g. when using IntervalMatch().

Re: QlikSense - Multiple Tables, Multiple Shared Fields

Wed, 09 Aug 2017 06:55:58 GMT

Hi Bill

Thank you for your reply. Our data warehouse that feeds Qlik Sense has a star schema layout. My main issue is in SQL I can easily join tables through multiple fields. So I can have a fact.Sales and a fact.Budget and join them using both Date and Location. The reason I want to do this in Qlik Sense is to ensure my end user doesn't have to select multiple selection boxes. (Location for Budget and Location for Sales, Date for Budget and Date for Sales etc)

The SQL (if this explains my issue better as I'm generally quite poor at explaining my intention) would be:

DECLARE @Date DATE = '2017-08-09'

DECLARE @Location VARCHAR(100) = 'London'

SELECT s.[SaleID],

s.[DateID],

s.[LocationID],

s.[Value]

FROM fact.[Sales] AS s

INNER JOIN dim.[Date] AS d ON d.[DateID] = s.[DateID]

INNER JOIN dim.[Location] AS l ON l.[LocationID] = s.[LocationID]

WHERE d.[Date] = @Date

AND l.[Location] = @Location;

SELECT b.[DateID],

b.[LocationID],

b.[SalesBudget]

FROM fact.[Budget] AS b

INNER JOIN dim.[Date] AS d ON d.[DateID] = b.[DateID]

INNER JOIN dim.[Location] AS l ON l.[LocationID] = b.[LocationID]

WHERE d.[Date] = @Date

AND l.[Location] = @Location;

I hope this makes sense, My only other thought is using variables but I haven't needed to do anything with them previously. Thank you for your time so far bill.markham‌ .