Self join using relationship mapping in Qlik

[Anonymous]

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]:

InvestmentCode	MetaData1	SomeMetaData
Investment123	foo	bar
Investment456	baz	zerk
Investment789	mer	prin
InvestmentABC	zee	gerk
InvestmentDEF	rem	zeb
InvestmentGHI	rak	bra

Excel [Relationship]:

InvestmentCode	FloatingLegCode	FixedLegCode
Investment123	Investment456	Investment789
InvestmentABC	InvestmentDEF	InvestmentGHI

[Anonymous · Accepted solution]

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];