Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

crissaegrim
New Contributor III

Self join using relationship mapping in Qlik

I would like to replicate the following join using Qlik syntax because one of my tables is SQL and the other is in Excel.  How can I do this?

SELECT inv.InvestmentCode

     ,tFloatLeg.MetaData1

     ,tFixedLeg.MetaData1

FROM [Investment] AS inv

INNER JOIN [Relationship] AS rel ON inv.InvestmentCode = rel.InvestmentCode

LEFT JOIN [Investment] AS tFloatLeg ON rel.FloatingLegCode = tFloatLeg.InvestmentCode

LEFT JOIN [Investment] AS tFixedLeg ON rel.FixedLegCode = tFixedLeg.InvestmentCode

Sql [Investment]:

InvestmentCodeMetaData1SomeMetaData
Investment123foobar
Investment456bazzerk
Investment789merprin
InvestmentABCzeegerk
InvestmentDEFremzeb
InvestmentGHIrakbra

Excel [Relationship]:

InvestmentCodeFloatingLegCodeFixedLegCode
Investment123Investment456Investment789
InvestmentABCInvestmentDEFInvestmentGHI
1 Solution

Accepted Solutions
crissaegrim
New Contributor III

Re: Self join using relationship mapping in Qlik

Hi.  I solved my own problem:

[Invesment]:

LOAD

    InvestmentCode

    ,MetaData1

    ,SomeMetaData

SQL SELECT

    InvestmentCode

    ,MetaData1

    ,SomeMetaData

FROM dbo.InvestmentTable;

//relationship mapping

LEFT JOIN

LOAD

    InvestmentCode

    ,FloatingLegCode

    ,FixedLegCode

FROM [lib://my_excel_lib/investment_relationship_sheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

//map the relationship to itself

LEFT JOIN

LOAD

    InvestmentCode AS FloatingLegCode

    ,MetaData1 AS SomeOtherMetaData

RESIDENT [Invesment];

1 Reply
crissaegrim
New Contributor III

Re: Self join using relationship mapping in Qlik

Hi.  I solved my own problem:

[Invesment]:

LOAD

    InvestmentCode

    ,MetaData1

    ,SomeMetaData

SQL SELECT

    InvestmentCode

    ,MetaData1

    ,SomeMetaData

FROM dbo.InvestmentTable;

//relationship mapping

LEFT JOIN

LOAD

    InvestmentCode

    ,FloatingLegCode

    ,FixedLegCode

FROM [lib://my_excel_lib/investment_relationship_sheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

//map the relationship to itself

LEFT JOIN

LOAD

    InvestmentCode AS FloatingLegCode

    ,MetaData1 AS SomeOtherMetaData

RESIDENT [Invesment];

Community Browser