9 Replies Latest reply: Jun 28, 2011 1:08 AM by Claudia Zwick RSS

    Tryin' to Understand Concatenate & Join

      Hey Everyone-

       

      I have two (2) tables that really should be one (1) table. However, due to some brilliant data integrity, field names are different and I cannot figure out if I need to concatenate or join.

       

      Table1:

      ID,  LastName

      1, Smith

      2, Johnson

      3, Fogherty

       

      Table2:

      ID, Last_Name (notice the underscore for Last_Name)

      4, Lord

      5, Stronghold

       

      I need to only have one table with only 2 fields: ID and LastName, but obviously from the contents of both Table1 and 2:

       

      OUTPUT

      Table3:

      ID, LastName

      1, Smith

      2, Johnson

      3, Fogherty

      4, Lord

      5, Stronghold

       

       

      I know this is a newbie question. So much apologies, I just have been slammin' my head against the desk tryin' to figure this out!

       

      Thanks!

        • Re: Tryin' to Understand Concatenate & Join
          John Witherspoon

          Post all the newbie questions you want in "New to QlikView".  I think that's what it's there for. 

           

          Anyway, I believe you want concatenate, but you'll need to rename the last name from the second table to match the first table so that QlikView realizes they are the same thing.  So...

           

          Table3:
          LOAD ID, LastName
          RESIDENT Table1
          ;
          CONCATENATE (Table3)
          LOAD ID, Last_Name as LastName
          RESIDENT Table2
          ;
          DROP TABLES Table1, Table2;

           

          In practice you probably won't actually need temporary tables 1 or 2.  Just do the table 3 load directly from their sources to avoid the temporary tables.

            • Re: Tryin' to Understand Concatenate & Join

              ACK! Thought I was posting on the newbie board. Apologies everyone.

               

              John... thanks for your help.  Really appreciate it.

                • Re: Tryin' to Understand Concatenate & Join
                  John Witherspoon

                  Heh, you did post on the newbie board.  I was trying to say that you'd done the right thing and there was no reason to apologize for posting a newbie question to the newbie board, and you should feel free to post as many as you want.  Unfortunately, what I said kind of came out sounding like the opposite!  Sorry about that. 

                    • Tryin' to Understand Concatenate & Join

                      Hi John,

                       

                      But where is the difference between Concatenate and Join?

                       

                      I used at least only Join and Left Join, because I don't understand the difference.

                       

                      Regards

                      vicky

                        • Tryin' to Understand Concatenate & Join

                          I struggled with this at first as well and I've found this blog post to be very informative on the subject

                           

                          http://www.qlikfix.com/2010/12/09/merging-tables-concatenation/

                           

                          (Sorry community admins if posting links to other sites is against the rules.....I'll be happy to edit this post if necessary.)

                           

                          To quote the most important line from that blog post:

                          ........the CONCATENATE prefix lets you add rows to a previously loaded table, while the JOIN prefix lets you add columns to a previously loaded table.

                           

                          Hope this helps!

                          Anosh

                            • Re: Tryin' to Understand Concatenate & Join
                              John Witherspoon

                              awadia wrote:

                               

                              To quote the most important line from that blog post:

                              ........the CONCATENATE prefix lets you add rows to a previously loaded table, while the JOIN prefix lets you add columns to a previously loaded table.

                               

                              That's probably a good rule of thumb to keep in mind, but as the inner and outer join examples show, it's not quite that simple.  You can use inner join to add columns and/or remove rows.  You can use an outer join to add columns and/or rows.  For instance, this would ONLY add rows, not columns:

                               

                              Table:
                              LOAD * INLINE [
                              Customer, Sales
                              Andy, 123
                              Becky, 234
                              ];
                              OUTER JOIN (Table)
                              LOAD * INLINE [
                              Customer, Sales
                              Becky, 234
                              Carla, 345
                              ];

                               

                              Customer, Sales
                              Andy, 123
                              Becky, 234
                              Carla, 345

                               

                              In this example, outer join is used to eliminate exact duplicate rows between the two sources.

                              • Re: Tryin' to Understand Concatenate & Join
                                John Witherspoon

                                awadia wrote:

                                 

                                Sorry community admins if posting links to other sites is against the rules.....I'll be happy to edit this post if necessary.

                                 

                                I'm not an admin and I've honestly never read the terms of use for the forum, but we post links to other sites all the time and I've never heard any grief.  I THINK that what QlikTech cares about is that people get good information, period.  The forum isn't a sales site where taking people to other sites would reduce sales.  If QlikTech believes in their product, and I'm convinced they do, then correct information, regardless of source, can only help them overall.

                                 

                                Not that ALL information about QlikView is directly good for sales, such as if we're discussing weaknesses of the product or why some other product might serve a particular person's needs better than QlikView.  But if the product overall is good, this sort of honest exchange is, in my opinion, good for QlikTech overall.  I've never seen any censoring even when we're discussing advantages of competitor products.  QlikTech seem like good people.  I'm sure there must be some negative interactions out there, but I've had nothing but positive interactions with them.

                              • Re: Tryin' to Understand Concatenate & Join
                                John Witherspoon

                                Concatenate just appends the records from the new source onto the end of the existing table.  There are many forms of join, but they all try to match up "key fields" between the two tables.  In QlikView's case, they match on all fields of the same name.  So let's take some examples.  First, here's our basic script.  The concatenate or join will go in the indicated spot:

                                 

                                Table:
                                LOAD * INLINE [
                                Customer, Sales
                                Andy, 123
                                Becky, 234
                                ];
                                // either concatenate or a join here
                                LOAD * INLINE [
                                Customer, Country
                                Becky, Canada
                                Carla, Mexico
                                ];

                                 

                                And here are the expected results for our various options.  Hopefully I got all these right.

                                 

                                CONCATENATE (Table)

                                Customer, Sales, Country
                                Andy, 123, null
                                Becky, 234, null
                                Becky, null, Canada
                                Carla, null, Mexico

                                 

                                ------------------------------

                                 

                                LEFT JOIN (Table)

                                Customer, Sales, Country
                                Andy, 123, null
                                Becky, 234, Canada

                                 

                                ------------------------------

                                 

                                RIGHT JOIN (Table)

                                Customer, Sales, Country
                                Becky, 234, Canada
                                Carla, null, Mexico

                                 

                                ------------------------------

                                 

                                OUTER JOIN (Table)

                                Customer, Sales, Country
                                Andy, 123, null
                                Becky, 234, Canada
                                Carla, null, Mexico

                                ------------------------------

                                 

                                INNER JOIN (Table)

                                Customer, Sales, Country
                                Becky, 234, Canada