Hi there,
I've seen a few posts on here regarding join issues but none seem to have the answer to my problem. Please bare with me as I try to explain my problem.
I have a table with Risk information as follows:
RiskVersion:
LOAD "(Re)InsuredName",
"(Re)InsuredID",
BusinessType,
Department,
Division,
ExpiryDate,
InceptionDate,
InsuredID,
InsuredName,
NumberOfVersion,
ReinsuredID,
ReinsuredName,
RevenueCode,
RiskID,
RiskReference;
SQL SELECT
"(Re)InsuredName",
"(Re)InsuredID",
BusinessType,
Department,
Division,
ExpiryDate,
InceptionDate,
InsuredID,
InsuredName,
NumberOfVersion,
ReinsuredID,
ReinsuredName,
RevenueCode,
RiskID,
RiskReference,
FROM dbo.RiskVersion;
Please note that there are rows in the RiskVersion table where there is either no InsuredID, ReInsuredID or Both. The Primary Key for this table is the RiskID. Whilst the Foreign Keys are InsuredID and ReInsuredID
I then load an Insured table which joins to the to the RiskVersion Table on InsuredID and I use a Right Join as I want all rows from the Insured Table. The load on Qlikview looks as below:
Right Join (RiskVersion)
LOAD AbbreviatedName AS [Insured - AbbreviatedName] ,
BEReference AS [Insured - BEReference] ,
ClientRole AS [Insured - ClientRole],
CreationDate AS [Insured - CreationDate] ,
DoNotUse AS [Insured - DoNotUse] ,
InsuredID,
InsuredRole AS [Insured - InsuredRole],
Name AS [Insured - Name],
ProvisionalClientRole AS [Insured - ProvisionalClientRole],
ReinsuredRole AS [Insured - ReinsuredRole],
ShortName AS [Insured - ShortName],
Territory AS [Insured - Territory],
ThirdPartyRole AS [Insured - ThirdParty],
UnderwriterRole AS [Insured - Underwriter];
SQL SELECT AbbreviatedName,
BEReference,
ClientRole,
CreationDate,
DoNotUse,
InsuredID,
InsuredRole,
Name,
ProvisionalClientRole,
ReinsuredRole,
ShortName,
Territory,
ThirdPartyRole,
UnderwriterRole
FROM dbo.Insured;
Once these two tables have been loaded into Qlikview, I present a table box made up of the RiskReference from the Riskversion table and a few fields from the Insured Table. As I expect the number of rows returned is exactly what im after.
I next load the ReInsured table. This again joins to the RiskVersion table on ReInsuredID and Is a Right Join as I want all records from the ReInsured Table. The load is as follows:
Right join (RiskVersion)
LOAD AbbreviatedName AS [ReInsured - AbbreviatedName],
BEReference AS [ReInsured - BEReference],
ClientRole AS [ReInsured - ClientRole],
CreationDate AS [ReInsured - CreationDate],
DoNotUse AS [ReInsured - DoNotUs],
InsuredRole AS [ReInsured - InsuredRole],
Name AS [ReInsured - Name],
ProvisionalClientRole AS [ReInsured - ProvisionalClientRole],
ReinsuredID,
ReinsuredRole AS [ReInsured - ReinsuredRole],
ShortName AS [ReInsured - ShortName],
Territory AS [ReInsured - Territory],
ThirdPartyRole AS [ReInsured - ThirdPartyRole],
UnderwriterRole AS [ReInsured - UnderwriterRole];
SQL SELECT AbbreviatedName,
BEReference,
ClientRole,
CreationDate,
DoNotUse,
InsuredRole,
Name,
ProvisionalClientRole,
ReinsuredID,
ReinsuredRole,
ShortName,
Territory,
ThirdPartyRole,
UnderwriterRole
FROM .dbo.Reinsured;
If I now try to present a table box with the RiskReference from the RiskVersion table and some fields from the ReInsured table. The number of records returned is incorrect and the number of records in the Insured Table Box also changes to an incorrect number.
Can anyone help?
Many thanks
