1 Reply Latest reply: Oct 6, 2016 2:50 PM by KHOA NGUYEN RSS

    Self join using relationship mapping in Qlik

    KHOA NGUYEN

      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
        • Re: Self join using relationship mapping in Qlik
          KHOA NGUYEN

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