Issue with syntables and masterdate linking

[#1]

Hi

I have a problem with syn tables which is causing some links to break. Sorry but this is a rather complex problem with issues both with the syntables and some linking which is wrong. This is a long read so you need time if you want to answer

$Syn 1 = Lnk_TOP_PolicyID+Lnk_TOP_RiskID
$Syn 2 = Lnk_TOP_PolicyID+Lnk_TOP_RiskID+Lnk_TOP_LossRatio_CoInsurerName
$Syn 3 = Lnk_TOP_PolicyID+Lnk_TOP_RiskID+Lnk_TOP_ClaimID
$Syn 4 = $Syn 1+$Syn 3
$Syn 5 = $Syn 1+$Syn 2
$Syn 6 = $Syn 1+$Syn 2+$Syn 3
$Syn 7 = $Syn 4+$Syn 5+$Syn 6

These are the main tables I use with the linked fields:

PolicyClient:
select * from  PolicyClient ---Lnk_TOP_PolicyID

PolicyRebrokering:
select * from  PolicyRebrokering---Lnk_TOP_PolicyID

PolicyReinstatements:
select * from  PolicyReinstatements---Lnk_TOP_PolicyID

ItemHeader
select * from  ItemHeader---Lnk_TOP_PolicyID, Lnk_TOP_RiskID

ItemDetails
SELECT  * from  ItemDetails---Lnk_TOP_RiskID

Claim
select * from  Claim---Lnk_TOP_ClaimID, Lnk_TOP_RiskID, Lnk_TOP_PolicyID

ClaimsDaily:
select * from  ClaimsDaily---Lnk_TOP_ClaimID, Lnk_TOP_PolicyID, Lnk_TOP_RiskID

DetailedFinancials:
select * from  DetailedFinancials---Lnk_TOP_PolicyID,Lnk_TOP_RiskID, Lnk_TOP_LossRatio_CoInsurerName

FinancialsOutstandingPremium:
select * from  FinancialsOutstandingPremium---Lnk_TOP_PolicyID

LossRatioClaim:
select * from  LossRatioClaim---Lnk_TOP_PolicyID, Lnk_TOP_RiskID, Lnk_TOP_ClaimID,Lnk_TOP_LossRatio_CoInsurerName

The problem is I cannot concatenate these,firstly because of issues that there will still be syn tables and then I still need to link the field Lnk_TOP_PolicyID on all the tables. The other issue is that I have created a masterdate table which uses Lnk_TOP_PolicyID, Lnk_TOP_RiskID and Lnk_TOP_ClaimID as field names. I then link these to dates

example
LOAD TOP_TransactionDate  as MasterDate,
Year(TOP_TransactionDate) as Year,
Month(TOP_TransactionDate) as Month,
Day(TOP_TransactionDate) as Day,
text(TOP_TransactionDate_FinYear) as FinYear,
text(TOP_TransactionDate_FinMonth) as FinMonth,
'FinTransactionDate' as DateType,
Lnk_TOP_PolicyID,
Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident DetailedFinancials

Most of the masterdate fields are fine(9 out of 13 dates are fine), for example if I use set analysis

sum({$
<DateType = {PolicyStartDate}, TOP_PolicyStatus = {'Active', 'Pending', 'Cancelled'}
>
}
  TOP_Policy_AnnualPremium +
  TOP_Policy_PolicyFee + TOP_Policy_BrokerFee + TOP_Policy_AdminFee)
 
This works perfectly when I select a Year

But when I get to Financials

sum({$
<DateType = {FinTransactionDate}
}
  TOP_Fin_DetailedPremium)
and I select FinYear 2012 it is not calculating it correctly, however when I have a list box  with the field TOP_TransactionDate_FinYear and I select 2012 from there it calculates it correctly. I have tried the Master date for FinYear both with and without the text in text(TOP_TransactionDate_FinYear)

I have even gone as far as testing the dates
example having listboxes for FinYear,DateType,and then having all the Dates in separate list boxes as stated above 9 of the 13 are fine,if I select a Finyear 2012 and then select PolicyStartDate it only shows 2012 on the Original StartDate field from PolicyClient, but when I select FinTransactionDate it shows me all years linked to DetailedFinancials.

Attached are all my Masterdate selects just so you have all the info

MasterDate:
LOAD TOP_Policy_StartDate  as MasterDate,
Year(TOP_Policy_StartDate) as Year,
Month(TOP_Policy_StartDate) as Month,
Day(TOP_Policy_StartDate) as Day,
text(TOP_Policy_StartDate_FinYear) as FinYear,
text(TOP_Policy_StartDate_FinMonth) as FinMonth,
'PolicyStartDate' as DateType,
Lnk_TOP_PolicyID,
'' as Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident  PolicyClient

Concatenate (MasterDate)
LOAD TOP_Policy_CancelledDate  as MasterDate,
Year(TOP_Policy_CancelledDate) as Year,
Month(TOP_Policy_CancelledDate) as Month,
Day(TOP_Policy_CancelledDate) as Day,
text(TOP_Policy_CancelledDate_FinYear) as FinYear,
text(TOP_Policy_CancelledDate_FinMonth) as FinMonth,
'PolicyCancelledDate' as DateType,
Lnk_TOP_PolicyID,
'' as Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident  PolicyClient

Concatenate (MasterDate)
LOAD TOP_Policy_Createddate  as MasterDate,
Year(TOP_Policy_Createddate) as Year,
Month(TOP_Policy_Createddate) as Month,
Day(TOP_Policy_Createddate) as Day,
text(TOP_Policy_CreatedDate_FinYear) as FinYear,
text(TOP_Policy_CreatedDate_FinMonth) as FinMonth,
'PolicyCreatedDate' as DateType,
Lnk_TOP_PolicyID,
'' as Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident  PolicyClient

Concatenate (MasterDate)
LOAD TOP_Policy_RenewalDate  as MasterDate,
Year(TOP_Policy_RenewalDate) as Year,
Month(TOP_Policy_RenewalDate) as Month,
Day(TOP_Policy_RenewalDate) as Day,
text(TOP_Policy_RenewalDate_FinYear) as FinYear,
text(TOP_Policy_RenewalDate_FinMonth) as FinMonth,
'PolicyRenewalDate' as DateType,
Lnk_TOP_PolicyID,
'' as Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident  PolicyClient

Concatenate (MasterDate)
LOAD TOP_Risk_InceptionDate  as MasterDate,
Year(TOP_Risk_InceptionDate) as Year,
Month(TOP_Risk_InceptionDate) as Month,
Day(TOP_Risk_InceptionDate) as Day,
text(TOP_Risk_InceptionDate_FinYear) as FinYear,
text(TOP_Risk_InceptionDate_FinMonth) as FinMonth,
'ItemInceptiondate' as DateType,
Lnk_TOP_PolicyID,
Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident  ItemHeader

Concatenate (MasterDate)
LOAD TOP_Risk_EffectiveDate  as MasterDate,
Year(TOP_Risk_EffectiveDate) as Year,
Month(TOP_Risk_EffectiveDate) as Month,
Day(TOP_Risk_EffectiveDate) as Day,
text(TOP_Risk_EffectiveDate_FinYear) as FinYear,
text(TOP_Risk_EffectiveDate_FinMonth) as FinMonth,
'ItemEffectivedate' as DateType,
Lnk_TOP_PolicyID,
Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident  ItemHeader

Concatenate (MasterDate)
LOAD TOP_Reinstatement_FullDate  as MasterDate,
Year(TOP_Reinstatement_FullDate) as Year,
Month(TOP_Reinstatement_FullDate) as Month,
Day(TOP_Reinstatement_FullDate) as Day,
text(TOP_Reinstatement_FinYear) as FinYear,
text(TOP_Reinstatement_FinMonth) as FinMonth,
'PolicyReinstatementsDate' as DateType,
Lnk_TOP_PolicyID,
'' as Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident  PolicyReinstatements

Concatenate (MasterDate)
LOAD TOP_Claim_DateofLoss as MasterDate,
Year(TOP_Claim_DateofLoss) as Year,
Month(TOP_Claim_DateofLoss) as Month,
Day(TOP_Claim_DateofLoss) as Day,
text(TOP_Claim_DateofLoss_FinYear) as FinYear,
text(TOP_Claim_DateofLoss_FinMonth) as FinMonth,
'DateofLoss' as DateType,
Lnk_TOP_PolicyID,
Lnk_TOP_RiskID,
Lnk_TOP_ClaimID
resident  Claim

Concatenate (MasterDate)  
LOAD TOP_Claim_DateRegistered  as MasterDate,
Year(TOP_Claim_DateRegistered) as Year,
Month(TOP_Claim_DateRegistered) as Month,
Day(TOP_Claim_DateRegistered) as Day,
text(TOP_Claim_DateRegistered_FinYear) as FinYear,
text(TOP_Claim_DateRegistered_FinMonth) as FinMonth,
'DateRegistered' as DateType,
Lnk_TOP_PolicyID,
Lnk_TOP_RiskID,
Lnk_TOP_ClaimID
resident  Claim

Concatenate (MasterDate)
LOAD TOP_Claim_DateFinalised  as MasterDate,
Year(TOP_Claim_DateFinalised) as Year,
Month(TOP_Claim_DateFinalised) as Month,
Day(TOP_Claim_DateFinalised) as Day,
text(TOP_Claim_DateFinalised_FinYear) as FinYear,
text(TOP_Claim_DateFinalised_FinMonth) as FinMonth,
'DateFinalised' as DateType,
Lnk_TOP_PolicyID,
Lnk_TOP_RiskID,
Lnk_TOP_ClaimID
resident  Claim

Concatenate (MasterDate)
LOAD TOP_ClaimDaily_Fulldate  as MasterDate,
Year(TOP_ClaimDaily_Fulldate) as Year,
Month(TOP_ClaimDaily_Fulldate) as Month,
Day(TOP_ClaimDaily_Fulldate) as Day,
text(TOP_ClaimDaily_FinYear) as FinYear,
text(TOP_ClaimDaily_FinMonth) as FinMonth,
'ClaimTransDate' as DateType,
Lnk_TOP_PolicyID,
Lnk_TOP_RiskID,
Lnk_TOP_ClaimID
resident  ClaimsDaily

Concatenate (MasterDate)
LOAD TOP_Policy_Rebrokering_StartDate  as MasterDate,
Year(TOP_Policy_Rebrokering_StartDate) as Year,
Month(TOP_Policy_Rebrokering_StartDate) as Month,
Day(TOP_Policy_Rebrokering_StartDate) as Day,
text(TOP_Policy_Rebrokering_StartDate_FinYear) as FinYear,
text(TOP_Policy_Rebrokering_StartDate_FinMonth) as FinMonth,
'PolicyRebrokeringNewDate' as DateType,
Lnk_TOP_PolicyID,
'' as Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident  PolicyRebrokering 
where TOP_RebrokeringType = 'New'

Concatenate (MasterDate)
LOAD TOP_Policy_Rebrokering_Cancelled_Date  as MasterDate,
Year(TOP_Policy_Rebrokering_Cancelled_Date) as Year,
Month(TOP_Policy_Rebrokering_Cancelled_Date) as Month,
Day(TOP_Policy_Rebrokering_Cancelled_Date) as Day,
text(TOP_Policy_Rebrokering_Cancelled_Date_FinYear) as FinYear,
text(TOP_Policy_Rebrokering_Cancelled_Date_FinMonth) as FinMonth,
'PolicyRebrokeringOldDate' as DateType,
Lnk_TOP_PolicyID,
'' as Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident  PolicyRebrokering
where TOP_RebrokeringType = 'Old'

Concatenate (MasterDate)
LOAD TOP_TransactionDate  as MasterDate,
Year(TOP_TransactionDate) as Year,
Month(TOP_TransactionDate) as Month,
Day(TOP_TransactionDate) as Day,
text(TOP_TransactionDate_FinYear) as FinYear,
text(TOP_TransactionDate_FinMonth) as FinMonth,
'FinTransactionDate' as DateType,
Lnk_TOP_PolicyID,
Lnk_TOP_RiskID,
'' as Lnk_TOP_ClaimID
resident  DetailedFinancials

Concatenate (MasterDate)
LOAD ''  as MasterDate,
'' as Year,
'' as Month,
'' as Day,
text(TOP_ClaimLossRatio_FinYear) as FinYear,
text(TOP_ClaimLossRatio_FinMonth) as FinMonth,
'ClaimLossRatioDate' as DateType,
Lnk_TOP_PolicyID,
Lnk_TOP_RiskID,
Lnk_TOP_ClaimID
resident  LossRatioClaim

[#2]

Is this just too long or has no one had to deal with a situation like this.

In short the issue is the syn tables. I have hasd suggestions to build concatenations of the values. There are two issues.

1. The original values still need to be used in building up the Masterdate or Calender table.

2. PolicyID for example is used alone in many of the tables, in other tables PolicyID and Risk ID are used together and on other tables, PolicyID, RiskID and ClaimID are used together.Evebntually you would still have syn tables, granted they would be less, but my concern would be the linking on tables with only one of the fields to tables with two of the fields and then also to those with all three the fields.

Any help or ideas would be helpful.