4 Replies Latest reply: Oct 22, 2018 8:46 AM by Vegar Lie Arntsen RSS

    Joining 2 fields from 2 tables

    Shakeeb Mohammed

      Hi Community,


      Hoping someone can assist me with the below-


      I have 2 tables that contain similar fields - e.g-


      Table 1 - 

      Date

      Supplier 

      Orders


      Table 2-

      Date

      Customer

      Orders 


      Effectively the supplier and customer need to be joined - both tables contain different supplier names , what I want to do is join this so when supplier is selected it will show supplier/customer and it’s relevant data. 

        • Re: Joining 2 fields from 2 tables
          Andy Weir

          Orders:

          Load

          Date,

          Supplier,

          Orders

          From table 1;

           

          Concatenate (Orders)

           

          Load

          Date,

          Customer,

          Orders

          From table 2;

           

          Regards

           

           

          Andy

          • Re: Joining 2 fields from 2 tables
            Vegar Lie Arntsen

            You have two possible solutions in QlikView/Qlik Sense.

            You could join your tables together using JOIN LOAD.

             

            //This code will generate the green table in the picture below
            Transactions:
            LOAD * FROM Supplier.qvd (qvd);  //Yellow data in picture
            JOIN (Transactions) LOAD * FROM Customer.qvd (qvd); ////Blue data in picture
            

             

            Alternative you could choose to generate a concatenated transaction table. Stacking the data into the same table resulting a data table similar to the combined blue/yellow table in the picture

             

            //This code will generate the blue/yellow table
            Transactions:
            LOAD * FROM Supplier.qvd (qvd);  //Yellow data in picture
            CONCATENATE (Transactions) LOAD * FROM Customer.qvd (qvd); //blue data in picture
            

             

            ConcatenateLoad.png

             

            The third option is to keep the two tables as they are, two seperate tables. You'll get a syntetic key that you don't need to worry about, or you could eliminate the syntetic key by replacing the two Date and Order-fields with an composite key eg

             

            LOAD:
            Date & '|' & Order AS %Key,
            Supplier
            FROM Supplier.qvd (qvd);
            
            LOAD 
            Date & '|' & Order AS %Key,
            Customer
            FROM Customer.qvd (qvd);
            

             

            Cheers

            Vegar

              • Re: Joining 2 fields from 2 tables
                Shakeeb Mohammed

                Hi Thanks for the info below -

                 

                I'm having some problems trying to solve this issue - i Should have mentioned in my original post -

                 

                Table 1 - comes from Microsoft SQL Server

                Date

                SupplierName

                OrderQty

                PlanQty

                ActualQty

                 

                Table 2 - Comes from Excel spreadsheet

                Date

                Supplier

                OrderQty

                PlanVolQty

                 

                I have created another Excel Spreadsheet that merges the supplier name from Table 2 to match with Table 1 -

                 

                E.g -

                 

                Supplier(Table2)Customer DescriptionCompany Description SupplierName(Table 1)
                F001-AldiAldiAldi

                F065 - APS Produce

                Aps ProduceAps Produce
                F041 - LactalisLactalis McLelland LtdLactalis McLelland Ltd
                F061 - Castle MacLellanPrimulaPrimula

                 

                 

                As the above table shows the customer Discription will not always match SupplierName.

                 

                So i have 3 tables that i need to join.