12 Replies Latest reply: Oct 17, 2014 10:37 AM by Richard Simpson RSS

    Exact working of JOIN in QlikView script?

    Friedrich Hofmann

      Hi,

       

      this is seemingly a very simple question, and more related to SQL syntax than to QlikView in particular, but the results in my current document confuse me a bit: What is the exact working of a JOIN implemented in a QlikView script?

       

      When I specify, before a load_statement, a LEFT JOIN([table] and specify, in brackets, the table that I want to join to, that should return the same nr. of records that I had in the table specified in brackets, shouldn't it?

       

      Thanks a lot for clarifying!

      Best regards,

       

      DataNibbler

        • Re: Exact working of JOIN in QlikView script?
          Alessandro Saccone

          A left join works as follow (IN qlik and SQL)

           

          Table a left join Table b

           

          where fields of table a : id, val and field of tab b : id and val1

           

          each id in tab a is taken and each id in tab b whose value is in tab a (repeated for all occurrences of tab b)

          • Re: Exact working of JOIN in QlikView script?
            sujeet singh

            It should return same no. of record in brackets.

              • Re: Exact working of JOIN in QlikView script?
                Friedrich Hofmann

                Hi,

                 

                that's exactly what I supposed - but it doesn't work that way. I have a table - table_A - that I want to join to a table named View_Pkg_Items. When I add

                 

                LEFT JOIN(View_Pkg_Items)

                 

                to the LOAD-Statement of table_A and delete the > Directory; < above it, then afterwards my table View_Pkg_Items  has about 10 times the nr. of records.

                Do I have to add a WHERE clause? I don't think so - the one field that matches is the join-field anyway, so the LEFT JOIN should throw out all non-matching records?

                 

                P.S.: Is there maybe a difference in the order in which I join tables? There is another table that I also join via a LEFT JOIN to View_Pkg_Items, it's named Item_Master. I don't know if I can join this new table to Item_Master and the resulting table to View_Pkg_Items? Would that make a difference, and do I have to rearrange the tabs of my script in some way to achieve that?

                  • Re: Exact working of JOIN in QlikView script?
                    Stefan Wühl

                    A JOIN or LEFT JOIN may increase the number of records.

                     

                    If you are using

                     

                    LEFT JOIN (TABLE1) LOAD A, B FROM TABLE2;

                     

                    and your key is field A, you ensure that you won't find a tupel (A,B) in the resulting relation where value A doesn't exist in your original TABLE1. So if you have something like

                     

                    TABLE1:

                    LOAD * INLINE [

                    A

                    1

                    3

                    ];

                     

                    LEFT JOIN (TABLE1)

                    LOAD * INLINE [

                    A, B

                    1,2

                    1,3

                    2,2

                    ];

                     

                    your resulting table will be

                     

                    A,B

                    1,2

                    1,3

                    3, NULL

                     

                    You will only find values in field A that already existed in the original TABLE1.

                    But your number of records increased from 2 to 3.

                     

                    Hope this helps,

                    Stefan

                      • Re: Exact working of JOIN in QlikView script?
                        Friedrich Hofmann

                        Hi swuehl,

                         

                        thanks for this hint! You're right, that is exactly my issue: The keyfield I'm using is not unique ("Perfect key") in the new table table_A that I want to join to the existing one - there are duplicate records (same keyfield, but different other_field), so those will be multiplied in the result table.
                        I only don't yet know how I can solve this.
                        P.S.: If the JOIN does not work, can I be certain that a regular association will produce a correct result? If not, that would be a mayor issue in my eyes as it would mean the associative logic of QlikView is generally not to be trusted, which would reduce its value to just about zero...
                          • Re: Exact working of JOIN in QlikView script?

                            Hi Friedrich,

                             

                            Create a composite key in both the tables that would make this association a unique one.

                             

                            It would solve your problem.

                             

                            -BR,

                            Abhinava

                            • Re: Exact working of JOIN in QlikView script?
                              Stefan Wühl

                              Please define 'correct result' (i.e. describe your input data a bit closer, also your expected outcome / requirements).

                               

                              It seems to me that your tables have a 1:n relationship. The way QV joins these tables is not much different from SQL.

                               

                              And remember, maybe you don't need to join these tables (like if you want to sum a field in TABLE2, NOT joining may return a correct result (depending on your requirements)).

                               

                              I remember some blog posts from Henric, Rob, et.al.

                               

                              Search this forum and the web for JOIN / CONCATENATE / Qlikkview. You should find something like this classic blog post:

                              http://qlikviewnotes.blogspot.de/2009/11/understanding-join-and-concatenate.html

                                • Re: Exact working of JOIN in QlikView script?
                                  Friedrich Hofmann

                                  Hi swuehl,

                                   

                                  thanks! I will look into that asap.

                                  I have worked with another software before that was also somehow based on SQL. I remember that for a LEFT JOIN to work, the relation should actually be n:1 - the key should be unique in my secondary table.

                                  Between my main table "Shipped" and the masterdata table I associate with it (via a Package_ID), the relation is 1:n because in the masterdata table, I have one more detail level (items) below the "Package" level of the keyfield. That is all right because I need all items within the packages.

                                  I tried to avoid further dimension (masterdata) tables linked only to the "primary dimension table" because that would contradict the ideal of a "star schema" in my data model.

                                   

                                  Now, in that "primary dimension table", the item_id is not unique because one item can be in multiple packages. Hmm... but in the next table, "Item_Master", the keyfield says "Primary key" and by a quick check via the >COUNT(DISTINCT)< function I just confirmed that every item_nr exists only once in that table. So that one is okay, the LEFT JOIN should work - as it does, I had seen it before.

                                   

                                  The next table, "Business_Plan", is another matter: There the keyfield is everything but unique - I have to find out what is the issue there. Maybe I have to include the date and use the latest (most current) record, I don't know.

                                   

                                  However, I would very much like to understand what is the issue with the associations - if they only work correctly on a unique key, but QlikView creates them automatically even with a non-unique key, that's an issue...

                                   

                                  Thanks a lot in any case!

                                  Best regards,

                                   

                                  DataNibbler

                                    • Re: Exact working of JOIN in QlikView script?
                                      Anders Moller

                                      Friedrich Hofmann wrote:

                                       

                                      However, I would very much like to understand what is the issue with the associations - if they only work correctly on a unique key, but QlikView creates them automatically even with a non-unique key, that's an issue...

                                       

                                      Thanks a lot in any case!

                                      Best regards,

                                       

                                      DataNibbler

                                       

                                      They work correctly in both cases, as they link what is linkable.

                                       

                                      Imagine a case where i have a bookstore and i want to see what books are being sold there.

                                       

                                      I have bookstore_id and store info in that table.

                                       

                                      In my other table i have bookstore_id, book_id and book info

                                       

                                      Now they are linked 1:N exactly as intended as each store could be selling the same books.

                                       

                                      Best wishes

                                        • Re: Exact working of JOIN in QlikView script?
                                          Anders Moller

                                          And here is a good link explaining the join, keep and concatenate options you have:

                                           

                                          http://community.qlik.com/thread/39177

                                            • Re: Exact working of JOIN in QlikView script?
                                              Friedrich Hofmann

                                              Hi Anders,

                                               

                                              okay, I will just believe that the associations work correctly - I couldn't quite believe anything different anyway, it would be too big an issue not to have been noticed and addressed ;-)

                                               

                                              I basically know how the JOIN works - I did NOT know  that detail that swuehl wrote though it is obvious - now ;-)

                                              Also, there is not the slightest hint in that book "QlikView 11 for Developers", which only shows a relatively simple example to demonstrate how the JOIN types work and does in no way deal with the possible complications.

                                               

                                              I will mark this as "solved" then.

                                               

                                              Best regards,

                                               

                                              DataNibbler

                                               

                                              P.S.: That link you posted is good - but it does not address that issue of the LEFT JOIN with a non-unique key in table_2, either.

                                                • Re: Exact working of JOIN in QlikView script?
                                                  Richard Simpson

                                                  Hi Nibbler

                                                   

                                                  This looks like an old post but not answered yet?

                                                  If you are trying to do a join but the "joining" table has multiple values that match... you can still do it but you have to instruct QlikView what to do for each field.

                                                  e.g. Sum the multiple values, Avg them? Or, if you know that there are lots of records but the value in the field is always the same, you can use Only(xxx).

                                                   

                                                  Hope that helps?