I think the general approach is the same (creating for each needed date-field an own calendar which then will be merged into the canonical-calendar) but it might be more difficult to find respectively (re-) create a working key-field for it (you might need several additionally steps to adjust and fill up the right/missing values).
do I create the working key field?
. For example, I have 3 date fields, 2 in one table and 3rd in 2nd table.
The two tables are (1) Associate with fields Associate, AssociateBirthDateTime, AssociateReadDateTime
and (2) Analyst with AnalystBirthDateTime
The AnalystBirthDateTime is the one I am unable to put in the same CanonicalDate with the other two date fields.
How do I join all three to create a canonical date? I am able to do the dates in the same table (ie AssociateBirthDateTime and AssociateReadDateTime), but fail with combining the third one. When I am trying to add the 3rd one, it will give a circular reference error because it is based on a different field. I have commented out the part which gives error.
This is my script:
Mapping Load Associate,AssociateBirthDateTime Resident [Associate];
Mapping Load Associate,AssociateReadDateTime Resident [Associate];
Mapping Load Analyst,AnalystBirthDateTime Resident [Analyst];
Load Associate,ApplyMap('Associate2AssociateBirthDate',Associate,null()) as CanonicalDate
Load Associate,ApplyMap('Associate2AssociateReadTime',Associate,null()) as CanonicalDate
// Load Analyst,ApplyMap('Analyst2AnalystBirthDate',Analyst,null()) as CanonicalDate
// Resident [Associate];
All three dates must be connected with the same key - this meant in all you need Associate or Analyst as single key respectively you need to create a new key-field from/with them and this could be quite difficult or even impossible if your tables didn't contain the right data respectively the data-quality isn't good enough.