8 Replies Latest reply: Nov 12, 2014 12:00 AM by anbu cheliyan RSS

    Left Join duplicates records

      Hi,

       

      In what situations would there be additional records after Left Join?

      Of course this is not what I want, but I have this issue.

      I used Distinct, Where Exists in the Left Join load, but it didn't help.

      There are still more records in the left table.

       

      Thank You!

        • Re: Left Join duplicates records
          Gysbert Wassenaar

          In any case where there are multiple matching records in the table on the right side of the join.

           

          Data:

          load * inline [

          Key, Value1

          A, 1

          B, 2];

           

          left join

          load Key inline [

          Key, Value2

          A, 10

          A, 12

          B, 15

          B, 16

          B, 20

          C, 30];

           

          The resulting table will look like

          Key, Value1

          A, 1

          A, 1

          B, 2

          B, 2

          B, 2

           

          If you joined both tables but also loaded the field Value2 you would have the same number of records, but no duplicate rows since the added Value2 field would make the records unique.

            • Re: Left Join duplicates records

              Hi,

               

              I don't quite understand your solution.

               

              My left table doesn't have unique key.

               

              It has:

               

              Data:

               

              Date

              [Customer Name]

              Balance

              ....

               

              I would like to add a customer number, from a file which has only customer number and customer name fields.

               

              my left join is:

               

              Left Join (Data)

              load

              [Customer Name],

              [Customer Number]

               

              Resident Customers;

               

              I expected to have the additional field  [Customer Number]  in Data table.

              I do, but there are additional rows. Doesn't look cartesian, though.

               

              Would appreciate your help here!

               

              Thanks!

                • Re: Left Join duplicates records
                  Gysbert Wassenaar

                  It's not a solution. It's an answer to your question: In what situations would there be additional records after Left Join?

                   

                  Ok, based on you last comment I think what happens is like this:

                   

                  Data:

                  load * inline [

                  Key, Value1

                  A, 1

                  A, 2

                  B, 2];

                   

                  left join

                  load * inline [

                  Key, Value2

                  A, 10

                  A, 12

                  B, 15

                  B, 16

                  B, 20

                  C, 30];

                   

                  The resulting table:

                  Key, Value1, Value2

                  A, 1, 10

                  A, 1, 12

                  A, 2, 10

                  A, 2, 12

                  B, 2, 15

                  B, 2, 16

                  B, 2, 20

                   

                  Every A from the first table is joined with every A from the second table. It's not a cartesian product because it still matches on value, but you do get a lot more records.

                   

                  You seem to indicate that that's not what you intend. So joining on only Customer Name is not enough. You'll have to add something to make the logic for the join explicit. Qlikview can't read minds yet so you have to tell it how to join the tables.

                • Re: Left Join duplicates records

                  Thank you it was helpful. How to avoid duplicates, when left join is used