Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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];
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];