5 Replies Latest reply: Nov 30, 2012 6:38 AM by Nayan Lalla RSS

    How to eliminate duplicate lines



      I am working on a QV Model to determine orders that are invoiced , partially invoiced and outstanding. 


      I have 4  tables namely:

      • Order Header
      • Order Detail
      • Invoice Header
      • Invoice Detail


      When an order is invoiced , it generates an invoice number, otherwise invoice number  its shown as "****NEW***. 


      However i have the following issue where the result I am achieving there are duplicates per line for orders that are invoiced.  See attached excel file called "Outcome Results"


      • The duplicate lines are highlighted in yellow in Table called "Current Result"
      • The ideal result i want to achieve is in Table called "Ideal Result"


      How do I apply the scripting so that I dont have duplicates.


      I have attached the excel file where the table sits as well as the QV Model


      Please can you help


      Thank you


      kind regards







        • Re: How to eliminate duplicate lines
          Peter Cammaert

          Your model has everything thrown together in a single table. That means that all lines with an OrderID occur twice:

          • once from the invoices data
          • once from the orders data

          That is, they occur twice if there are corresponding invoices. For example ORD002 has no invoices, so no duplicates. Use Table Viewer->Preview to check your data.


          Your solution is to use QlikView as ... QlikView: construct an associative model with a few tables and appropriate keys.





            • Re: How to eliminate duplicate lines

              Hi Peter



              Thank you for your rersponse.


              I am new at Qlikview and at scripting, hence I do not how to create an associative model. I just know basics.  In the model that i have attached previously, can you assist with the scripting.


              Thank you


              kind regards


                • Re: How to eliminate duplicate lines
                  Peter Cammaert

                  Hi Nayan,


                  try this:


                  • Load and join orders and orderlines in a table and call this one Orders
                  • Load and join invoices and invoicelines in a second table and call this one Invoices.
                  • While loading this last table, give all fields a prefix to avoid linking them to the corresponding fields in Orders.
                  • Except for OrderID. That field should be used as a key tying the two together.


                  Teaching you how to write Load scripts would take me too far. But hey, QlikView has excellent Designer/Developer trainings that teach you how to master QlikView in a few days. Make sure to look into them if you want to do some serious development in QlikView.