Self join using relationship mapping in Qlik

Anonymous — Thu, 06 Oct 2016 14:11:32 GMT

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

Excel [Relationship]:

InvestmentCode	FloatingLegCode	FixedLegCode
Investment123	Investment456	Investment789
InvestmentABC	InvestmentDEF	InvestmentGHI

Re: Self join using relationship mapping in Qlik

Anonymous — Thu, 06 Oct 2016 18:50:51 GMT

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