Hi all, I'm fairly new to Qlik and hoping for assistance with sorting out the best approach to my situation. I am trying to build a data model to compare planned (budgeted) hours on projects to actual time logged by staff assigned to the project. My projects have start and end dates which vary widely and overlap. Actual hours worked are logged daily and sometimes logged to a specific large project, but for smaller projects, the time can be logged to a more generic time log "group" that can relate to more than one project creating a many-to-many relationship.
Based on all of the blogs, documents, etc. I have read through, it appears to be recommended that I use Generic Keys to combine my two fact tables (planned hours & actual hours) into one fact table. It also appears that I need to use Interval Match to create a bridge table between my actual hours logged date and my project start and end dates. Separately, both of these seem fairly straightforward.
My dilemma:
Because I have a many-to-many relationship with my Interval Match, the technical brief on that topic says that I should not join the bridge table to my fact tables. However, due to the concatenation recommended for budget to actual situations, I would only have one combined fact table, and not two separate ones that need to be connected by the interval match bridge table. The guidance on these two situations seem to conflict with each other and I'm unsure of how best to proceed in a way that won't create sizable/complicated synthetic keys or loops in my data model.
Help?
