Table Joins

Wed, 26 Sep 2012 15:32:02 GMT

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

Re: Table Joins

Gysbert_Wassenaar — Thu, 27 Sep 2012 09:29:36 GMT

By using right joins you create one table that has records for every InsuredID and every ReInsuredID even if there were no corresponding records in RiskVersion. That's what right and left joins do. I guess that's probably not what you expected. See http://community.qlik.com/thread/39177 for some more information.

Re: Table Joins

Thu, 27 Sep 2012 10:44:39 GMT

Thanks Gwassenaar, the information definitely helps.

topic Re: Table Joins in QlikView

Table Joins

Re: Table Joins

Re: Table Joins