Link to generic calendar

[bullish35]

I am using various date fields across the tables in a dashboard. I have a generic calendar but have not been able to link the dates and calendar without creating 'loosely coupled relationships' and/or synthetic keys. Any assistance getting this sorted out would be greatly appreciated.

For example, I want to link the activity_date in the Paid_ACP table to the master calendar; and, I'd like to link both the submit_date and bridge_date fields in the Policy table to the master calendar.

[johnw · Accepted solution]

Let's say you have data like this currently (and I'm totally guessing at the unique key for each table - each table should have a unique key, and that unique key is what you should use):

Paid_ACP:
pd_said_code, activity_date
PA1, Jan 10

Opportunity_Illustration:
illustration_plan_id, sent_date
OI1, Jan 15

Policy:
ss_policy_number, submit_date, application_date, bridge_date
P1, Jan 5, Feb 12, Feb 20

Meeting:
rn_interations_id, appt_date
M1, Feb 1

Claims4HowTo:
Ex_Claim Number, Ex_Issue Date, Ex_Reported Date
C1, Jan 20, Jan 15

You could probably link these to the Calendar like this:

CalendarLinkage:
CalendarDate, DateType, pd_said_code, illustration_plan_id, ss_policy_number, rn_interations_id, Ex_Claim Number
Jan 10, Paid Activity Date, PA1
Jan 15, Opportunity Illustration Sent Date, , OI1
Jan 5, Policy Submit Date, , , P1
Feb 2, Policy Application Date, , , P1
Feb 20, Policy Bridge Date, , , P1
Feb 1, Meeting Appt Date, , , , M1
Jan 20, Claim Issue Date, , , , , C1
Jan 15, Claim Reported Date, , , , C1

I believe that will give you a central linking table and no loops. Whether you still keep the separate dates on each table is up to you. I typically either choose separate dates and separate calendars, or a single date and a single calendar, but they CAN be mixed.

[#3]

maybe you can use link tables...

but i'm not quite sure about it..

[bullish35]

Ok, this is definitely making sense, John. So I need to create a new table (e.g., CalendarLinkage), pull in the table-specific date and key fields referenced, add a field for DateType, and depending on which table I pull from, assign a DateType to each record.

The primary scripting command will be join; is that right?

[johnw]

Concatenation instead of joins. Something like this:

CalendarLinkage:
LOAD
pd_said_code
,'Paid Activity Date' as DateType
,activity_date as CalendarDate
RESIDENT Paid_ACP
;
CONCATENATE (CalendarLinkage)
LOAD
illustration_plan_id
,'Opportunity Illustration Sent Date' as DateType
,sent_date as CalendarDate
RESIDENT Opportunity_Illustration
;
etc.

[bullish35]

I believe this is going to work, sir. I will verify tomorrow. Thank you!

[bullish35]

This did it. I was able to exclude the dates from the separate tables' load sccripts and created the CalendarLinkage table by loading the appropriate fields directly from the QVDs we'd already created for each of those tables. Tidied up the table structure dramatically. I will upload my sample QVW with the new script in a day or so. Perhaps someone else will be able to benefit as well. Thanks again!

[bullish35]

The location of the original solution upload was posted prior to the community site revamp. I've uploaded it again. Once it has been through the approval (by Moderator) process, I'll add the new link. In the meantime, let's see if I can upload it directly to this post.