2 Replies Latest reply: Oct 23, 2016 2:29 AM by Deepanshu Chamoli RSS

    SQL Joins

    Deepanshu Chamoli

      Hello All

       

      Please help me in the below queries.

       

      Query1. I have the SQL table and Excel file. I want a left join between them based on three lookups. But when i am sourcing it in SQL Query it shows an error. My main issue is that I want a join in SQL Table not by making a composite key. I want it in below way as I have three lookups

       

      LIB CONNECT TO 'TIGUNVMBGWL0011';

       

      AccountTemp:

      LOAD

          PGLD,

          CLN,

          DIN AS DAN,

          MAN AS MN,

          ALID

      FROM [lib://Files/AT.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

      LOAD CCD,

          CIN,

          DIN,

          MAN,

          ACLID;

      SQL SELECT

        CCD,

          CIN,

          DIN,

          MAN,

          ACLID

      FROM tempdb.dbo."Case" C

      LEFT JOIN AccountTemp A ON A.CLN = C.CIN AND A.DIN = C.DAN AND A.MAN = C.MN ;

       

      Query 2: Is it possible two make a join of two resident table based on three lookups with where clause.

       

      As for E.g.

       

      I have two resident table Account & Case

       

      MAIN:

       

      LOAD *

      RESIDENT ACCOUNT A

      LEFT JOIN CASE B ON A.A = B.B AND A.C = B.D AND A.E = B.F

      WHERE A.A IS NOT NULL;

       

      Please suggest.

        • Re: SQL Joins
          Stefan Wühl

          The SQL SELECT of your first query is sent to the SQL driver and executed by the driver / you DBMS.

           

          Your resident table AccountTemp is part of the Qlik resident data model and unknown outside Qlik system, i.e. unknown to your SQL driver.

           

          You need to join both tables within Qlik, using JOIN LOAD prefixes.

           

          Within Qlik, you implicitely define the key fields for your JOIN by using same field names in both tables to join (remember to name every other field not to join differently in both tables).

           

          If you want to apply a WHERE clause to either table, you can do this.