Qlikview will link your tables based on FieldNo and Date because those fields have the same name.
If that is correct you relationship is fine.
If not, you have to know what relates to each other and name the fields accordingly.
In this case it looks like only FieldNo should be used for the relationship, but you have to figure that out.
it is making the joining but with the following warning
One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.
I would say you have two options, namely:
- Use a Link Table between the 2 Fact Tables and the two dimension tables
- Use unassociated Date Dimension en rename Date fields in Fact so that only FieldNo associates. You will then use each Fact table's dates in Set Analysis in the expressions.
To create a link table, select the distinct FieldNo's and Dates into a 'Link Table'. Create a key with these two fields that will link back to the Fact Tables (so you will need to create the key in the Fact Tables as well). Use the FieldNo and Date columns in the Link Table to link back to the dimension tables.
Hope this makes sense.
As suggested by Hesten what you need is a linktable
see the example by John...calendar must be common where as dates must be having a datetype in the linktable
I think the second solution provided by Hesten is good as compair to creating different Date types, becaue if multiple date types is created into link table then set operator need to be used for Date type in expression if 'Field' column is shared with LatexCollection and TreesPlantation Field values, and using set operator would impact the performance of application if fact data is huge. I have attached the solution qvw for this appraoch.
In the example in the linked thread, the DateType field and set analysis are only required to handle the case where a single key field has more than one date field associated with it, and we want to preserve that information.
I see nothing wrong with your example, but it isn't designed to handle that case.
simpler requirement = simpler solution