I have 2 QVDs with the SAME FIELD NAMES but different sources and different values. I have created a QVW with the 2 tables combined using concatenate() and added in a data link and a master calendar. But my dates are still incorrect. What can I be doing wrong?
Here is my script of the QVDs
This the Calendar and DateLink
For example: When I select the Month of Feb. I want to see just the Estimates with a Feb. date and Rewards with a Feb Date. This is what I am getting below:
HOWEVER.....when I don't combine the QVDs and just load one data source and create a master calendar it works properly. What am I doing wrong?
Re: Canonical Date not working when combing 2 QVDS
Part of the problem here is the link table you are using, which uses only the estimate number to join back to estimates. If you look at estimate number 5761 this explains the problem.
You select the month of February in your master calendar.
One of the dates in February finds a match in the link table, and that provides a EstimateNumber of 5761.
You are then using this to join back to the Estimate table, and at this point it finds three rows of data, each with very different dates, but that is irrelevant because you have only joined back to this table on the EstimateNumber.
I would need more information to understand why the other rows are appearing (those with NULL values in the reward date column).
The problem is that you are trying to model something using a single date when you are dealing with multiple date types, I think you will need to use set analysis to create the solution you need. So I would remove the link table, keep the master calendar (but it would not be joined to your Estimate table), I think I would then create a set analysis aspect to your expressions in your table that says EntryDate = Feb OR RewardDate = Feb.