4 Replies Latest reply: Jan 15, 2016 3:04 PM by Massimo Grossi RSS

    Confused with join

    Matt Pierce

      Hi There,

       

      I'm really confused about the Join function in scripts.   I've read several blog entries and the help topics but I'm still not getting it.

       

      Take this script:

       

      Table1:

      Load  * Inline [

      Key, Column1 , Column2

      A,1,4

      A,2,5

      A,3,6

      ];

       

      Table2:

      Load  * Inline [

      Key, Column3, Column4

      A,11,44

      A,22,55

      A,33,66

      ];

       

      Join (Table1)

      Load

      *

      Resident Table2;

       

      Drop table Table2;

       

      I would expect the product of this to be a nice table something like this:

       

      KeyColumn1Column2Column3Column4
      A141144
      A252255
      A363366

       

      But instead I get something akin to a Cartesian join.    This has me stumped.  Both table share a key field (Key!) why am I getting duplicate rows?

       

      Help!

        • Re: Confused with join
          Massimo Grossi


          because the common field, used in the join, is Key and, a join is used for combining fields from two tables (or more) by using values common to each

           

          for every A in the first table, with the join you get 3 rows in the second table --> 9 rows

           

          try this if you only want 3 rows

           

          Table1:

          Load  * Inline [

          Key, Column1 , Column2

          A,1,4

          AA,2,5

          AAA,3,6

          ];

           

          Table2:

          Load  * Inline [

          Key, Column3, Column4

          A,11,44

          AA,22,55

          AAA,33,66

          ];

           

          inner Join (Table1)

          Load

          *

          Resident Table2;

           

          Drop table Table2;

           

           

           

          1.png

            • Re: Confused with join
              Matt Pierce

              Thanks maxgro that's helped solidify it in my mind.  The data in the field must be unique. 

               

              I've checked my app and it is (in principle) unique.   The common field across both tables I'm trying to join is a combination of emplid and date that is returning duplicates.

               

              However, it turns out that the data quality is an issue (there are some unintentional overlapping dates) meaning the field isn't actually unique at all.

               

              Thanks once again

            • Re: Confused with join
              Gabriel Ferran

              The default join is the inner join. It combines each key with values equals in the other table.

              As maxgro said you need to have diferent values for each key value. If you don't you obtain a cartesian join because each value in  the first table combines with all values in the second.

                • Re: Confused with join
                  Massimo Grossi

                  AFAIK the default join in Qlik is the outer because, below is from the Qlik help, the outer is optional

                   

                  The explicit Join prefix can be preceded by the prefix outer in order to specify an outer join. In an outer join all combinations between the two tables are generated. The resulting table will thus contain combinations of field values from the raw data tables where the linking field values are represented in one or both tables. The outer keyword is optional.

                  outer join [ (tablename ) ](loadstatement |selectstatement )