Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Derek_T
Contributor III
Contributor III

Why is this an error in Data Model ?

Disclaimer, I'm new to Qlik and I'm trying to figure out what's wrong with such a model below.

Qlik does not like such loop, so it seems I could either use "Master Calendar" to link table 1 and 2 OR "Unique IDS".

Now let's say I just use "Master Calendar" to link the tables, if I filter Table1 on Owner A  I'm still going to get all the Owner of Table 2 matching the same date.

And vice versa if I use "Unique IDS" instead I loose the connection on date. 

These are standard type of query though and I'm sure there's a way to do it in Qlik but I don't know how.

Capture.PNG

 

Thanks !

Labels (2)
1 Solution

Accepted Solutions
Derek_T
Contributor III
Contributor III
Author

I found a solution to my problem:

I created a "Link" table using Unique IDs and Master Calendar on which all tables are connected. 

Load Distinct
ID
resident Unique IDs;

Join
Load Distinct
DATE
Resident Master_Calendar;

And created the Key = ID & '-' DATE in the Link Table as well as Table1 and Table2.

Only issue I have with such an approach is that the Link Table size grow very quickly, especially if such a key involve more than 2 fields. So I'm still open to suggestion if there's a more efficient approach, I don't know if the Link table is stored in memory are recomputed every time it's queried.



View solution in original post

13 Replies
marcus_sommer

Do not try to link these fact-tables just concatenate (union in sql) them - maybe adding an extra source-field to be able to differentiate with selections or set analysis to from which source which data come from.

- Marcus

MartW
Partner - Specialist
Partner - Specialist

Qlik uses an associative model. meaning when clicking a value other tables associated with this update with only those values.

also for good practice fieldnames need to be unique so that there are no sync tables. with regards to the looping,

try and create unique key values for your tables 1,2 and Unique IDS. after that also create those Key's for the Calender table.  this should fix the problem

Derek_T
Contributor III
Contributor III
Author

Thanks Marcus, I think that's the part I don't understand would you have any link to refer me to please ?

Coming from PowerBI I thought this was how the data model worked, but I have the feeling it's not the exact same logic here.

Derek_T
Contributor III
Contributor III
Author

Yes that's what I did, sorry it does not show in my mock up example. But in Qlik Table1 and Table2 have column Date1 and Date2 (same for ID) and the Master calendar as column Date1 and Date2 as well.
So they are unique keys and Qlik is not trying to associate Table1 and Table2 (with synthetic key on Date and ID.)

But even then, it still does not like the that Table1 and Table2 are connected through 2 different tables. Whereas it makes perfect sense to me to be able to filter on a date range AND an ID (or Owner).

It's been a couple of years since I used PowerBI but to my recollection these kinds of connections were allowed in the model.

 

MartW
Partner - Specialist
Partner - Specialist

@Derek_T, can you share a screenshot of the datamodel viewer?

marcus_sommer

Like you have already observed a key on ID or Date wouldn't associate all table-parts else just the ones on which they are linked. A possible bypass would be to combine both fields within a single field for the association and renaming the origin fields to avoid the creation of synthetic keys or circular references.

But it's often not enough because now you couldn't access both fields within the UI easily because they have different names and also connecting these tables with other dimension-tables.

Further linking tables didn't solve any challenges if the relation-ship between the tables isn't 1:1 and/or if there are missing keys on one or even on both sides. Quite often it ends in creating a linking table between the fact-tables which contains the distinct concatenated keys from all fact-tables and on which the further dimensions are linked.

By larger datasets this could become a performance-problem because such link-tables could grow much bigger as the fact-tables itself. Therefore why doing it more complicated as just concatenating the facts directly? Within the most scenarios it worked very well, is simple to create and leads to the officially recommended star-scheme data-model (as best compromise between efforts, maintainability and performance).

- Marcus

Derek_T
Contributor III
Contributor III
Author

Here it is :

Capture.PNG

Derek_T
Contributor III
Contributor III
Author

Thank you, does that mean Qlik data Model does not support 1:n or n:n relationship ?

marcus_sommer

Of course it's possible to build valide data-models with such relationships but depending on the data and the requirements it might not always be trivial to merge them with join-approaches in the script and/or associating those tables in the data-model. Especially if there are also the mentioned missing keys - which would mean the attempt to link a value against NULL which couldn't work. That's not seldom, for example if you have sales without budgets or reverse - you won't be able to get a complete picture of them if any keys points against NULL.

By simply adding such datasets with a concatenating respectively union all the linking-challenges wouldn't be there - neither between the fact-tables nor in regard to associate any dimensions. It's really so very simple.

- Marcus