topic Re: Canonical Date Across Different Tables in App Development

Canonical Date Across Different Tables

Tue, 16 Aug 2016 21:54:31 GMT

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 — Wed, 17 Aug 2016 05:49:58 GMT

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

Thu, 18 Aug 2016 18:14:21 GMT

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];

Re: Canonical Date Across Different Tables

marcus_sommer — Fri, 19 Aug 2016 05:14:53 GMT

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.

- Marcus

Re: Canonical Date Across Different Tables

Anonymous — Wed, 19 Apr 2017 09:14:57 GMT

I like this post!

Can I create a new birdge table with the following fields?

ID (New field autogenerated)
Key of the Associate table
Key of the Analyst table

NOTE: I'm very new, just trying to learn because I have a similar situation.

Re: Canonical Date Across Different Tables

marcus_sommer — Wed, 19 Apr 2017 09:35:48 GMT

I think an autogenerated ID with recno() or rowno() will be rather not helpful but an autonumber() on a (composite) key might work but like above mentioned it will be depend on the associations and the data-quality of your tables if it worked to chain the events.

- Marcus