topic Re: Circular Reference and Composite Keys in QlikView

Circular Reference and Composite Keys

ZainZaidi — Thu, 16 Jan 2020 06:54:45 GMT

I have a problematic data model. There is a circular reference present by default.

I have considered making a bridge table but then all tables will be linked to bridge table using a 3 key composite key. However, the tables are connected with each other at 2 key composite keys. Another problem is of granularity. Table1 has data at the highest level of granularity (Item). Table2 has a lower granularity level (Item - Carton). And Table3 has an even lower granularity (Carton-Container).

What is the correct method for handling this situation? Generic keys? I'm stumped.

Re: Circular Reference and Composite Keys

Vegar — Thu, 16 Jan 2020 07:14:23 GMT

It's hard to give an absolute answer not knowing your data, but I'll give a suggestion. You say that table 1 has the highest granularity and therefore I would consider removing the A_D_Key from table 3, the lowest granularity table. Hopefully you will still have access to all the correct A_D_Key values.

Re: Circular Reference and Composite Keys

Vegar — Thu, 16 Jan 2020 07:17:46 GMT

Another solution if you have a hierarchy relationship between the three tables is to remove the A_C_Key in the Table 1.

Good luck with your data modelling.

Vegar

Re: Circular Reference and Composite Keys

ZainZaidi — Thu, 16 Jan 2020 07:28:44 GMT

Hi Vegar, I really appreciate the quick response.

Like you said, i've removed the A_D_Key but i'm worried whether this is logically correct or not.

I don't know if this will result in lost data during association.

The relationship is not hierarchical so i cannot remove A_C_Key.

Thanks for the suggestion though.

Re: Circular Reference and Composite Keys

Vegar — Thu, 16 Jan 2020 08:18:23 GMT

A simple check you can do is to rename the A_D_Key instead of removing it. Then you can create a validation object in QlikView comparing the output of A_D_Key and your renamed version of A_D_Key. It will give you an indication of the quality of your new data model.

Re: Circular Reference and Composite Keys

marcus_sommer — Thu, 16 Jan 2020 13:12:58 GMT

Personally I wouldn't tend to link all of these tables else I would try to merge them. It may be more or less simple joins/mappings depending on the relation of their keys and/or how to handle missing keys (maybe checking in beforehand and adding them before the join/mapping).

Logically more easier would be just to concatenate all these tables. Non-synchron tables and/or tables with a different granularity work often quite well in Qlik - therefore just give it a try.

- Marcus