Selecting fact table rows when a link table has been created
Hi,
in my QV 10 document, I have some dimensions and fact table and one link table. Each dimension key has a different name between dimension and fact table; the link table acts like a mapping table. I have created a list box to put the key values of a dimension, fe Year; Year in the fact table has another name and both names for Year are present in the link table. When I select an year of my list no rows are selected in the table box to show the measures. This issue doesn't occur when I use the year field of the fact table, but so in the year list box I haven't all possible values present in the year dimension table, that I'd like to have.
If I read your original question correct, I think you have 1 fact table and loops to dimensions. In that case, neither link tables or concatenation will be what you are looking for. You also state that YEAR has a different name in the dimension and fact table. If you were to use link tables, you typically create a synthetic key between a fact table and a link table.
For you actual question, I'm assuming 2 possible scenario's regarding a loop with YEAR:
- there is a loop because you point to two fields in your time dimension from the same fact
- there is a loop because you use year twice from the same fact (like startyear, endyear?)
Like I said, in either way, link tables won't help you. If I understand your problem correct, there are two possible solutions:
- create a seperate YEAR dimension for the second use, with a different name (related to it's meaning?). There is usually no need to have both references point to the same dimension.
- derive a measure from the difference in year. You would keep reference to your time dimension as f.i. the startyear and a measure years_passed, enabling you to reconstruct the endyear if needed.
if I missed the point you really have to be more clear on the structure you are trying to implement.
