8 Replies Latest reply: Mar 6, 2014 12:18 PM by Jonathan Ditchfield RSS

    Joining tables in Qlikview

      Hi,

       

      I require a bit of help on joining tables in Qlikview.  Due to a lack of Indexes on the tables, it is impossible for me to create join on the tables in SQL without crashing the database server.  The only way I can pull out my information is to load the tables in that I require and then try and get Qlikview to join the tables, which I have done to some success.

       

      However I now have an issue where my userdb_location table has a location field where I need to pull out the data CLO.  I need to pull out only the callref field from table Opencall_Table where location = CLO.  Please see attached the Table view.

       

      Is this possible in Qlikview, if so how would I start going about this.

       

      Regards,

      Jon Ditchfield

      export.png

        • Re: Joining tables in Qlikview
          Alessandro Saccone

          I give you the code to perform that operation:

           

          MyTab:

          noconcatenate

          Load * from Opencall_Table ;

          left join

          load * from userdb_location;

           

          MyNewTab:

          Load * resident MyTab

          Where location = CLO;

           

          Drop table MyTab;

           

          Hope it helps

          • Re: Joining tables in Qlikview
            neetha P

            Hi Jonathan,

             

            I think best solution will be Applymap() function,where we can retreived data for only one field

            Performance wise its good.

            if you use Join it takes lot more time to retreive data as whole data is retreived

            • Re: Joining tables in Qlikview
              Massimo Grossi

              using applymap to filter


              // load dimension

              Userdb_location:

              load .....................;

               

              // create filter: table with 2 column, cust to join with Opencall_table and location to filter

              Map: Mapping LOAD cust_id, location resident Userdb_location;

               

              // load facts filtering by location of cust_id

              Opencall_Table:

              load

                   *

              where

                // applymap(mapping table name, field in input, value if not found) returns the second column

                ApplyMap('Map', cust_id, 'N/A') = 'CLO'

              ;

                • Re: Joining tables in Qlikview

                  I have the following load script, however I cannot get it to work properly,  would someone be able to help me with this please?

                   

                  MyTab:
                  LOAD callref,
                       status,
                       priority,
                       owner,
                       cust_id,
                    firstname,
                    surname,
                    keysearch as cust_id,
                    location;

                  SQL SELECT  callref,
                        status,
                        priority,
                        owner,
                        cust_id
                  FROM mydata.open;

                  SQL SELECT firstname,
                     surname,
                     keysearch,
                     location
                  FROM mydata.user;

                   

                   

                  Regards,

                   

                  Jon

                    • Re: Joining tables in Qlikview

                      Jon,

                       

                      Can you try this:

                      MyTab:
                      LOAD callref,
                           status,
                           priority,
                           owner,
                           cust_id,
                      SQL SELECT  callref,
                            status,
                            priority,
                            owner,
                            cust_id
                      FROM mydata.open;

                      Left Join (MyTab)

                      LOAD

                        firstname,

                        surname,

                        keysearch as cust_id,

                        location;

                      SQL SELECT firstname,
                         surname,
                         keysearch,
                         location
                      FROM mydata.user;

                       

                      Fabrice