5 Replies Latest reply: Apr 19, 2017 5:35 AM by Marcus Sommer RSS

    Canonical Date Across Different Tables

    Sid Panda

      Hi,

      I have been able to create a canonical date as per discussion here (Canonical Date)

      But this is about setting up a canonical date for only ONE table with all the date fields in the SAME table. What if I have date fields scattered across multiple tables. How do I create a canonical date field for that?

      Thanks

        • Re: Canonical Date Across Different Tables
          Marcus Sommer

          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).

           

          - Marcus

            • Re: Canonical Date Across Different Tables
              Sid Panda

              marcus_sommer

              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:


              Associate2AssociateBirthDate:

                Mapping Load Associate,AssociateBirthDateTime Resident [Associate];

              Associate2AssociateReadTime:

                Mapping Load Associate,AssociateReadDateTime Resident [Associate];

              Analyst2AnalystBirthDate:

                Mapping Load Analyst,AnalystBirthDateTime Resident [Analyst];

               

              DateBridge:

              Load Associate,ApplyMap('Associate2AssociateBirthDate',Associate,null()) as CanonicalDate

              Resident [Associate];

              Load Associate,ApplyMap('Associate2AssociateReadTime',Associate,null()) as CanonicalDate

              Resident [Associate];

              // Load Analyst,ApplyMap('Analyst2AnalystBirthDate',Analyst,null()) as CanonicalDate

              // Resident [Associate];