I have 2 different data sources that require me to report up to the hour. Each source has a time/date field which links to a calendar using an intervalmatch for each. The calendar has start & end times (unique names) for table 1 to join, and the same setup for table 2.
This works well on its own. There is however a requirement to link in an employee table (using employee numbers). This is is effectively a lookup table for both these tables as well. the lookup has been structured to use a unique field name for each employee # based on source table 1, and another unique field name to link table 2. The moment i add the employee table it creates a loop. Can anyone suggest how to get this working please? It has stumped me for close to 2 days now. Structure below (it contains classified information so I am unable to add a model):
The short answer is - you need to concatenate your two Fact tables into a single Concatenated Fact, and to name your timestamp field in the same way, and combine the two IntervalMatch relations into one. This will resolve your issues.
However, you will need to resolve other possible issues that come with concatenated facts - your two facts will not be linked anymore, when they are concatenated in a single table. The full answer to this question contains all QlikView data modelling techniques.
If you'd like to learn more about QlikView data modelling, I'd recommend my new book QlikView Your Business. In the book, I explain in a lot of detail how to work with multiple Fact tables and how to build Concatenated Facts properly.