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):
PA1, Jan 10
OI1, Jan 15
ss_policy_number, submit_date, application_date, bridge_date
P1, Jan 5, Feb 12, Feb 20
M1, Feb 1
Ex_Claim Number, Ex_Issue Date, Ex_Reported Date
C1, Jan 20, Jan 15
You could probably link these to the Calendar like this:
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.
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?
Concatenation instead of joins. Something like this:
,'Paid Activity Date' as DateType
,activity_date as CalendarDate
,'Opportunity Illustration Sent Date' as DateType
,sent_date as CalendarDate
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!