4 Replies Latest reply: Oct 14, 2015 9:27 AM by Shambhu Belakeri RSS

    Left Join Causing duplicates

    Matt Maino

      I am left joining a new table to my main table via an account number and a production date. My new table can contain more than one of the same account number/production date combinations (because of mistakes in database entry). This is causing the data in the first table to be duplicated for each instance of the same account number/ production date in the second table. Looks something like this:

       

      Table 1

      ACCT_NBR     PROD_DT     BALANCE

      12345               10/13/15          $100

       

      Table 2

      ACCT_NBR     PROD_DT     NAME

      12345               10/13/15          SAM

      12345               10/13/15          SAMANTHA


      Resulting Table

      ACCT_NBR     PROD_DT     NAME          BALANCE

      12345               10/13/15          SAM               $100

      12345               10/13/15          SAMANTHA     $100


      Is there any way to handle this in QlikView so that if there is an error in the data the balance will not be counted twice?


      Thanks!

        • Re: Left Join Causing duplicates
          Henric Cronström

          All joins - including the left join - can cause duplication of records. You should use Applymap instead. See Don't join - use Applymap instead

           

          HIC

          • Re: Left Join Causing duplicates
            Jonathan Dienst

            I would usually use ApplyMap, but here is another alternative to catch all the names:

             

            Left Join ([Table 1])

            LOAD ACCT_NBR, PROD_DT, Concat(NAME, ', ') As NAMES

            FROM [Table 2]

            GROUP BY ACCT_NBR, PROD_DT

            ;

            • Re: Left Join Causing duplicates
              rohit gupta

              Hi try below code

               

              Table 1

              Load ACCT_NBR  ,   PROD_DT ,ACCT_NBR &*PROD_DT  as key ,  BALANCE from xyz.qvd;

               

              left join

              Load ACCT_NBR &*PROD_DT  as key ,   Maxstring(NAME) as NAME from abc.qvd grup by ACCT_NBR  ,   PROD_DT,

               

              hope it helps.

              Thanks

              Rohit

              • Re: Left Join Causing duplicates
                Shambhu Belakeri

                Mathew,

                 

                You will get duplicate records because there are two different names for same acc no and date. Hence what you can do is make repeating column value as zero. I have written for the same.

                 

                Table1:

                LOAD [ACCT_NBR ],

                     PROD_DT,

                     BALANCE

                FROM

                [C:\Users\bshambu\Desktop\QLIK.xlsx]

                (ooxml, embedded labels, table is Sheet2);

                 

                Left join(Table1)

                Table2:

                LOAD [ACCT_NBR ],

                     PROD_DT,

                     [NAME ]

                FROM

                [C:\Users\bshambu\Desktop\QLIK.xlsx]

                (ooxml, embedded labels, table is Sheet3);

                 

                 

                Final_Table:

                Load

                   [ACCT_NBR ] as Acc_No,

                   PROD_DT as Prod_Date,

                   [NAME ]as Per_Name,

                   If(Previous([ACCT_NBR ]) = [ACCT_NBR ] and Previous(PROD_DT) = PROD_DT, BALANCE,0) as Prod_Bal

                   Resident Table1;

                 

                 

                Drop Table Table1;

                    

                Acc_No Per_NameProd_BalProd_Date
                12345 SAMANTHA $100.0010/13/15
                12345 SAM 010/13/15

                 

                Best Regards

                Shambhu B