topic Re: Data Model Help - Circular referenceD in App Development

Data Model Help - Circular referenceD

bha — Mon, 23 Jul 2018 15:09:08 GMT

Hi there I hope you can help. I am pulling my hair out a little with my data model, it seems straightforward and is I am sure almost there. I have set up this scenario to try and make it easier to get it explained. I am trying to link data from 2 tables (systems), 'DevAvHandled ' and 'DevMagLogged'. There is no link currently between these except a mismatched 'Name'.

So I am trying to link it to a new table. where the staff name is spelt correctly and is the primary key (name_uq) and there are 2 fields in there, one called MagUser_id which should link to the DevMagLogged table on DevMagLogged.username and one called AvaAgentLogin in which links to DevAvHandled.AgentLogin

So far so good.

I then have a calendar table which needs to link to both DevMagLogged, and DevAvHandled on DevMagLogged.magDate, and DevAvHandled.AvaDate. I cant use the auto generated calendar here

So I guess I also need, which I have tried, to link the Dates between DevMagLogged and DevAvHandled

I have probably not explained this very clearly! I have done it step by step, and keep ending up with a circular reference somehow or another. It was almost working and if I filtered on avaDate or MagDate, it was doing ok. When I put the calendar in and do week commencing for example, one of the tables refreshes, but not the other.

I hope that makes sense

Here is a picture of the data model as it is now. Al the tables (views) are in a local sql server instance. I have also attached a script for the test tables from SQL2014, any help on how it should be done/if I need to explain anything, very much appreciated!

Thanks a million

Re: Data Model Help - Circular referenceD

bha — Mon, 23 Jul 2018 15:10:43 GMT

And from the data manager

Re: Data Model Help - Circular referenceD

bha — Tue, 24 Jul 2018 08:14:08 GMT

Hi there, I am just wondering if this is posted in the correct place and indeed makes sense or if there is any further information which may be usefu

have a great day

Re: Data Model Help - Circular referenceD

bha — Wed, 25 Jul 2018 10:53:06 GMT

One more shameless bounce. I am so stuck with this, and am sure it must be straightforward.

I think I'm just not understanding something and thinking of relational databases.

I have restarted to try and understand and removed my calendar table, trying it with the autogenerated one

My problem is that the calendar seems to associate with each of my tables (views) without taking into account the dates in the other table, so I get 2 date associatons, which independently work on each one, but not updating the other. The results returned are just a fixed value in the table I am not filtering on, or currently changing, but way to high on the measures on one of the tables

I'm not getting a circular reference right now, just everytime I try and link as I would expect to, I get circular refs

I would be so grateful if someone could assist!!

I am aware that the screen shot (table/field names) is different to the original one, but the problem remains the same

I have attached the current data data model view, and also the load script

Re: Data Model Help - Circular referenceD

bha — Wed, 25 Jul 2018 11:51:37 GMT

An example .

Re: Data Model Help - Circular referenceD

bha — Thu, 26 Jul 2018 08:03:21 GMT

I have moved this to a different forum as perhaps I havent explained properly in the "New to Qliksense" area

Re: Data Model Help - Circular referenceD

bha — Wed, 22 Aug 2018 15:16:47 GMT

Right, if anyone could help me out here I would be totally grateful, I have been away from it a bit

I went back to square one if you like, to try and get my head round this, and for some reason something as simple getting the calendar dimensions working. My approach may be totally wrong due to my database mindset which I am trying to change. This is the method I am trying and would like to understand.

My table vLinkTable is actually a sql view which should I believe achieve what I want in a manner I understand for now

Script is attached and image below and quite clearly I am creating synthetic keys. #

So there are 2 fact tables if you like (at this stage), a link table, and the $Syn table

So I know you arent meant to have synthetic keys/circular references anyway.

However the data which is coming back at this stage is correct.

So how come, even with this (and I understand its probably not the best), that I cant filter on the week_commencing field in the vLinkTable?. calendar_date work fine so I guess something must be right (both the measures from the facts mag_LoggedbyAgent and ava_handledByAgent. So I guess something is working right

username links to magusername

AgentLogin links to AvaAgentLogin

magDate amd avaDate link to calendar_date

I am probably not explaining very well (brain is circular referencing itself ). Does this make sense with the script?

sunny‌