3 Replies Latest reply: Feb 2, 2015 3:30 AM by Peter Cammaert RSS

    How to join two tables without common Key Field

      Hi,

       

      can any body explein, how to join two tables without common key field between them.

       

       

       

      Thanks,

      Chennaiah

        • Re: How to join two tables without common Key Field
          Anand Chouhan

          Hi,

           

          If you have no common keys then you can go for concatenate between table or simply use Join or Outer Join. and also you an use the Autonumber() function for creating the common composite keys for join.

           

          1. Autonumber(Field1,Field2,Fleid3) as Key

          2. Field1&'_'&Field2 as Key //But need to check it generate unique key combinations.

          3. Join the table with use of Distinct key word.

           

          Regards

          Anand

          • Re: How to join two tables without common Key Field
            Jonathan Dienst

            Hi

             

            You can join two tables without a common key, but because there is no key, the join will be a cartesian product, with every row in table 1 associated with every row in table 2. If you join table 1 with 100,000 rows to table 2 with 10,000 rows you will land up with 1,000,000,000 which will possibly cause out of memory errors and is very likely not what you want.

             

            If your issue is that there is a key between the tables, but the fields are named differently then you must rename the field of one table to match the field in the other table at load time. Something like:

             

            Table1:

            LOAD ...

                 ProductCode,

                 ...

            ;

             

            Table2:

            LOAD ...

                 ProductID As ProductCode,

                 ...

            ;

             

            Or perhaps there is no association between the fields? If so, why would you join them?

             

            HTH

            Jonathan

             

            PS: you may not need a join, just a commonly named key field to associate the values in table1 and table2.

            • Re: How to join two tables without common Key Field
              Peter Cammaert

              You don't really need a single common key field. You can perform JOINs with 2 or more individual fields that are present in the two tables like for example Customer ID, Product ID, Seq No, etc. and without merging them. QlikView will figure out how to map identical values in different fields and correctly merge corresponding rows.

               

              However, if you don't have any common field at all, you'll get what Jonathan says: a cartesian product which may blow up your desktop or server