4 Replies Latest reply: Aug 29, 2011 4:36 AM by Ester PERETS RSS

    JION IGNORING FILED

      Hi,

      how do i join tabels ignoring a filed whit  tha same name.

      on one table i have the customer and branches (more then one to customer)

      on the other i have customer branches (one of the branches)and sum

      I want to join only on customer that the sum will duplicate it self by customer and all the branches will be in on column

       

      Thanks,

      Ester

        • Re: JION IGNORING FILED
          Miguel Angel Baeyens de Arce

          Hello Ester,

           

          When you join two tables using the LOAD clause, you can rename fields you don't want to be used as key fields in the joining. Think of something like this

           

          SalesAndCustomers:
          LOAD CustID,
               Date,
               Amount
          FROM File.qvd (qvd);
          JOIN LOAD CustID,
               BranchID,
               Amount AS BranchAmount // renaming this field will prevent joining using this one in addition to the CustID
          FROM Branches.qvd (qvd);
          

           

          You can adapt it to your model.

           

          Hope the idea makes sense.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

            • JION IGNORING FILED

              Hi Migurl,

              I wont to leave BranchAmount in the same column

              Ester

                • Re: JION IGNORING FILED
                  Miguel Angel Baeyens de Arce

                  You cannot. When you join tables, QlikView uses all fields named alike to make the joining, so if you don't want some of these to be part of the composite key that will be used to join both tables, you will have to rename them. You can link the tables instead of joining them, since you have some key fields in both tables, and the associative logic should do the rest:

                   

                  Customers:
                  LOAD CustID,
                       Date,
                       Amount AS CustomerAmount
                  FROM File.qvd (qvd);
                  
                  Sales:
                  LOAD CustID, // this will link this table with the previous one by means of this field
                       BranchID,
                       Amount AS BranchAmount
                  FROM File.qvd (qvd);
                  

                   

                  Or you can concatenate them, or use mapping tables to add some fields to a previous table without joining them should you don't need each of the fields in the second table or in case this second table doesn't have a lot of fields. Anyway, it would be very useful if you could post some sample data and waht do you expect, and how are you getting that so far.

                   

                  Miguel Angel Baeyens

                  BI Consultant

                  Comex Grupo Ibérica