Issue with syntables and masterdate linking

Tue, 22 May 2012 07:24:37 GMT

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

Issue with syntables and masterdate linking

Wed, 23 May 2012 06:16:41 GMT

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.

topic Issue with syntables and masterdate linking in QlikView

Issue with syntables and masterdate linking

Issue with syntables and masterdate linking