5 Replies Latest reply: May 9, 2014 5:11 AM by Stefan Wühl RSS

    Inner Join Problem

    Saad Tadili

      Hi,

       

      I'm trying to join 2 tables in a database. I don't want to join them using the INNER JOIN clause of the database but instead I want to use the Inner Join clause of qlikview

       

      the tables are as follow

       

      Table 1

      Champ1Champ2
      A1
      B2
      C3

       

       

       

      Table 2

      Champ2Champ3
      2X
      3Y
      4Z

       

       

       

      What I want to obtain in Qlikview is :

       

      Table 3

      Champ1Champ3
      BX
      CY

       

       

      I tried doing it with the following instructions :

       

      [Table3]:

      LOAD

          *;   

      SQL SELECT

          [Champ1],

          [Champ2]

      FROM

          AdventureWorksDW2012.dbo.[Table1];

         

      Inner Join ([Table3])

       

      LOAD

          *;

      SQL SELECT

          [Champ2],

          [Champ3]

      FROM

          AdventureWorksDW2012.dbo.[Table2];

         

       

      The problem is that I get the following result :

       

      Table 3

      Champ1Champ2Champ3
      B2X
      C3Y

       

      And I don't want the column named champ2 (the join key is useless for me)

       

       

      I tried with the following instruction :

       

      [Table3]:

      LOAD

          [Champ1],

          [Champ3];   

      SQL SELECT

          [Champ1],

          [Champ2]

      FROM

          AdventureWorksDW2012.dbo.[Table1];

         

      Inner Join ([Table3])

       

      LOAD

          *;

      SQL SELECT

          [Champ2],

          [Champ3]

      FROM

          AdventureWorksDW2012.dbo.[Table2];

         

      but I get the following error :

       

      Field not found - <Champ3>

      SQL SELECT

          [Champ1],

          [Champ2]

      FROM

          AdventureWorksDW2012.dbo.[Table1]

       

       

      I anyone can help it would be nice of him.

       

       

      thanks

        • Re: Inner Join Problem
          Stefan Wühl

          Just drop the field at the end of your script:

           

          DROP FIELD Champ2;

            • Re: Inner Join Problem
              Saad Tadili

              Hi,

               

              It does work, but what if I made a join on 7 fields for example, I would have to drop the 7 fields which is a bit cumbersome, isn't there a way to select only the fields I want instead of dropping those I don't want.

               

              Thank you.

                • Re: Inner Join Problem
                  Stefan Wühl

                  It would probably be nice if QV syntax were closer to SQL, but it doesn't allow to join already the input tables, then project the fields you want to see only.

                   

                  Like in SQL:

                   

                  SELECT column_name(s)

                  FROM table1

                  INNER JOIN table2

                  ON table1.column_name=table2.column_name;


                  Instead, you join a resident table with an output table of a second load (like in your example Table3 with the output from Table2 load). And, QV determines the keys just by looking at same field names in both tables, you can't explicitely state the key fields for the join.

                   

                  Two alternatives to what I proposed above that came to my mind:

                   

                  a) Do a resident load of your Table3 with the fields you want, then drop Table3:

                   

                  [Table3]:

                  LOAD

                      *; 

                  SQL SELECT

                      [Champ1],

                      [Champ2]

                  FROM

                      AdventureWorksDW2012.dbo.[Table1];

                   

                  Inner Join ([Table3])

                   

                  LOAD

                      *;

                  SQL SELECT

                      [Champ2],

                      [Champ3]

                  FROM

                      AdventureWorksDW2012.dbo.[Table2];

                   

                  Result:

                  LOAD Champ1, Champ3 RESIDENT Table3;

                   

                  DROP TABLE Table3;


                  b) JOIN in your SQL DB.

                    • Re: Inner Join Problem
                      Saad Tadili

                      Hi,

                       

                      Thank you for your answer.

                       

                      For the sake of simplicity I used the same database for the example above, but in reality the two databases are not on the same instance and I can't use database links (it is forbidden by security policies), so I can't make the join at the database level.

                       

                      Concerning your answer a) this is exactly what I was looking for, the only thing that I'm afraid is the behavior of qlikview memory wise. Say the size of Table3 is 3GB (compressed) and the size of Table3 (with only Champ1 and Champ3) is 2GB, is it possible that at a given point in time Qlikview will use 5GB or ram.

                       

                      Thanks